Email Excel Range Using Lotus
May 27, 2009
I have yet to find ANY information online relating specifically to emailing any given range in Excel via Lotus Notes. The codes that do exist out there either fail to fully copy the range or relate to attaching an excel file into a Lotus Notes Memo.
Below I have copied the code the I am working with, but I keep getting the following error: Error # 438, Object doesn't support this property or method. After making some minor tweaks, it might then give me another error, Error 424 Object Required, but the range then doesn't copy into the Lotus Notes memo at that point.
I have highlighted the area that I think the code is failing at, that being the actual act of copying/pasting the range selected into the Lotus Notes Memo. With the current code, the selected range does get fully copied into a new memo in Lotus notes, but these errors populate in Excel.
Can anyone offer any suggestions to resolve this issue? I have spent hours on this and I'm sure there are many others out there that might be able to benefit from a solution as well.
Function LotusEmailQuote() ....
View 9 Replies
ADVERTISEMENT
Mar 14, 2011
I am wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
View 9 Replies
View Related
May 3, 2009
I have searched high and low on the forum and can not seem to find a solution to my problem. Here is my problem (besides being new to Lotus Notes): I need a button on my excel worksheet that gets information from Column H in the attached spreadsheet and puts all the emails in the To: (could be >than 100 email addresses) Field in Lotus Notes. I would like to have the subject read "New Email from Your Basin Coordinator" and have the CC list come from a second worksheet (CC).
View 4 Replies
View Related
Aug 6, 2014
I'm trying to create an excel macro that can send emails with an attached file per row of my entry in excel I imagine it would be like the screenshot below. I've tried the code in the link below from a macro tutorial website but wasn't able to make it work due to error DataObject is unkown I'm using excel 2013 and lotus notes 8.5 as my email.
View 9 Replies
View Related
Feb 1, 2013
When i click a button in excel, i need the table from a certain "Range" to copy/paste in the body of an e-mail that will send itself automatically. Now, i know how to build the macro to automatically send the e-mail but i can't find a way to insert the table in it. here's the code i'm using to send the e-mail:
Public Sub SendMail2()
'Tools --> references --> Microsoft Scripting runtime
'Variables
Dim EMailSendTo As Variant
[Code].....
But i don't know how to adapt the code for my problem.The fact is, i've just started to write macros and the code i'm using for the e-mail isn't from me, i have juste adapted it for my problem.
View 2 Replies
View Related
Oct 29, 2013
I have an Excel sheet that I use as a mailing automatism for reports. As it currently is it attaches an actual copy of the excel workbook to the email and send them out. The mailer contains several different people, and they get different report each day. Due to the size of some of the files, I am starting to run into an issue where I cannot sent the emails anymore because they are too big, so I am wanting to switch to sending links to the files instead, and I have hit a wall.
I use Lotus Notes 8.5. The VBA will cycle through a range, and each cell has a list of report delimited by a ",". It takes the list and passes it to the mailer as a string. The mailer takes the string, turns it into an array and splits it out, and then checks to make sure the reports are current. One email could have up to 10 different reports in it. I have tried creating an HTML MIME email to include the links.
Here is the code I currently have:
Code:
Sub Send_HTML_Email(ByRef Name As String, ByRef Address As String, ByRef Reports As String)
Const ENC_IDENTITY_8BIT = 1729
'Send Lotus Notes email containing links to files on local computer
Dim NSession As Object 'NotesSession
Dim NDatabase As Object 'NotesDatabase
Dim NStream As Object 'NotesStream
Dim NDoc As Object 'NotesDocument
[code]...
View 1 Replies
View Related
Jun 2, 2014
I have an excel file that will be downloaded by multiple users and saved locally. I was wondering if there was a way to build code into the file so that everytime it is saved an email would be created in lotus notes from the user to me with a copy of the file attached. Is this possible?
View 2 Replies
View Related
Oct 30, 2006
I have developed many macros that generate automatic emails from excel using Outlook, with the excellent help I've found here. Now due to recent buyouts, we have changed our email from Outlook to Notes. I'm looking for the simplest method of changing my macros so they will work with Notes. Below is the typical code I'm currently using. Is there an easy way to change it for Notes?
Public Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim Dist As String
Dim supplierinfo As Range
Dist = WorksheetFunction.VLookup(Supplier, Workbooks("ScarForm.xls").Sheets("Supplier Information").Range("a9:z1000"), 3, 0)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.to = Dist
.CC = "emailnames"
.BCC = ""
.Subject = "email subject"
.Body = "Text of email"
Windows(Fname & ".xls").Activate
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
View 9 Replies
View Related
Sep 16, 2013
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
View 5 Replies
View Related
Aug 18, 2014
I am looking to build a macro that can automatically email a recipient upon their name being selected from a drop down list. It designed to be for a warehouse, so when new packages come in we can scan them in and have excel send an email out once our receiving team determines who it is for. With their being multiple recipients, I would like the code to have an IF type function that choosing the correct recipient. the Column titles "Recipient/Dept" is where the dropdown list is located.
It would be ideal for the code to just utilize an open Lotus Notes Session and use that to send the email.
Attached is the document with what I have thus far : Warehouse barcode test.xlsm
View 14 Replies
View Related
Nov 8, 2007
I have a macro that sends a custom email through lotus notes. It works, but will only generate 1 email instead of looping through my list. If I have "next r" at end I get email of 1st record. If I put "next r" before "On Error GoTo Audi"
I get last recorded emailed but not 1st 2. I don't know how to get it to send all. It seems the for next loop is jacked up.
For you to see macro work you need to have lotus notes and put email address in column B.
test file is attached.
View 10 Replies
View Related
Feb 18, 2013
I have found the code that I need to send one spreadsheet or the entire workbook via lotus notes through VBA, but I cannot seem to find the way to email 2 specific spreadsheets anywhere..
I have tried both of these, but I get an error when running:
Code:
With Worksheets("Diario") And Worksheets("Periodo")
.Copy
stFileName = Worksheets("BD").Range("A1").Value & "- NDG " & Worksheets("Code").Range("K22").Value
Debug.Print stFileName
End With
Code:
With Sheets(Array("Diario", "Periodo"))
.Select
Selection.Copy
stFileName = Worksheets("BD").Range("A1").Value & " - NDG " & Worksheets("Code").Range("K19").Value
Debug.Print stFileName
End With
I would like the 2 sheets (Diario & Periodo) to be copied both to the same temporary workbook...
View 1 Replies
View Related
Jul 17, 2013
Any ways to automatically send emails using Lotus Notes.
On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:Program Fileslotus
otesdatasig.htm), and when I comment out the part where strSignature is mentioned there is no signature.
My code is below:
Sub mySub()
Dim x As Integer
Dim UserName As String
Dim MailDbName As String
Dim Recipient As Variant
Dim Maildb As Object
Dim MailDoc As Object
[Code] ...........
View 1 Replies
View Related
May 12, 2006
I managed to create the attached macro with help from stuff I've found in the internet. With this code, I am able to save the file and then send an email cia Lotus Notes. I need two things:
(1) The code to attach the file saved into the email that it sends
(2) The code so that the file is protected so that changes are not made by the recipient.
HERE IS THE
ActiveSheet.Copy
ActiveWorkbook.SaveAs filename:="V:TFMCO8 ReportingHistory" & [c22].Value & " " & Format$([c20], "- (YYYY-MM-DD)") & ".xls"
ActiveWorkbook.Close False
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen Then
Else: Maildb.OpenMail...........
View 9 Replies
View Related
Dec 28, 2007
I have Excel 2003 and Lotus Notes Version 6 and I need to send out the active workbook from Excel. I have searched through the existing threads, but all the code that I run results in errors -
View 9 Replies
View Related
Feb 10, 2009
I wrote this code years ago to Auto Send Emails from Outlook...
Sub Emailfile()
Dim OutlookApp As Object
Const olMailItem = 0
Set OutlookApp = CreateObject("Outlook.Application")
Dim EmailContent As String
With OutlookApp.CreateItem(olMailItem)
EmailContent = Range("A1").Value
.To = "Test"
.Subject = "LSG Data File"
.Body = EmailContent
.Attachments.Add ("P:MI TeamICCE LEADS EXTRACTSLSGLSG Data File")
End With
End Sub
I can't figure out how to get it working for Lotus notes... So 2 things I guess really... Firstly is it possible? Second: If it is, how can I get it to send say 16 emails each with a specific attachment but the same commentary?
View 9 Replies
View Related
Feb 13, 2004
Finally I have the code all okay for Sending Reports, and other extra comments etc...
And now...
Is there code available for picking up images ( Screen snaps) and inserting into an email in Notes after the Body of the Message?
Either a reference to a link or some code would be greatly appreciated, cos I cannot find any info at this point in the archives ( Presuming I have searched using proper parameters ).
View 9 Replies
View Related
Oct 12, 2004
I am trying to send 7 different emails in this sub, the sendnotes sub is not recognizing my GOLOOP value
As you can see I stole this code from this site. You all are terrific!
Sub SendFiles()
Sheets("Menu").Select
Range("C23").Select
Filename = ActiveCell.Value
GoLoop = "1"
SendNotesMail
GoLoop = "2"
SendNotesMail
GoLoop = "3"
SendNotesMail
GoLoop = "4"
SendNotesMail...................
View 9 Replies
View Related
Feb 27, 2008
1. I dont want this to auto send. I want it to compose the mail and leave it ready to hit the send button in lotus notes. How do I do that?
2. Would like to include multiple emails. If I put two emails (separated by comma or semicolon into cell C19, Lotus notes sends as one address and it bounces. (This is not terribly important, but would be a nice feature)
3. Would like to insert the signature line that's already configured on lotus notes. Since this auto sends the email, the signature is not added. Any idea how to do this?
4. Now this would be really nice, tell the macro which database to use. The one I want to use is not "my" email, but a shared email. It lives on server "Notes1/recovery" , in folder "mail" with a database named "company.nsf". Is there a way to do this?
Here is my code that works, but needs the above features.
Sub SendNotesMail()
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim Recipient As String
Dim Subject1 As String
Dim ccRecipient As String
View 9 Replies
View Related
Mar 2, 2009
putting a macro button on an individual worksheet so the user can click a big easy button and a new email will pop up in Lotus notes with the worksheet attached for them to input the address and then send the worksheet..
I prefer to:
Send individual worksheets only
The ability to input the email address is the new composition email in LOTUS Notes
View 9 Replies
View Related
May 28, 2013
below is the code i use to create a lotus note email.
i would like it to display my email and send it manually instead of sending out automatically.
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
[Code].....
View 4 Replies
View Related
Oct 15, 2009
The following line of code is part of the great work that this forum did in creating a macro to send an automated email through Lotus Notes.
I have a small tweak that requires the Body of the message to exist on sheet other than the one where the macro is launched.
The originial code had:
Body1 = Replace(Join(Application.Transpose(Range([by9], [by18].End(3))), "@") & "@@Thank you,", "@", vbCrLf)
I tried:
Body1 = Replace(Join(Application.Transpose(Sheets("LookUp Sheet").Range([by9], [by18].End(3))), "@") & "@@Thank you,", "@", vbCrLf)
But the body of the email is not being copied.
Am I putting the calling the Sheets reference incorrectly?
The routine works perfectly if the body data comes from the sheet where the macro is launched?
View 9 Replies
View Related
Mar 2, 2010
I want my macro to be able to mail the active workbok through outlook using the email addressses in Cell R1 and having the body of the email have the text in cell R2:R8?
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim range as range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("R1:R8").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = ws.Range("R2:R8").Value
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
View 9 Replies
View Related
Sep 29, 2009
The following will place values found in O19,020,&022 into an e-mail body.
I'm attempting to place a selected range of cells into the body also.
To do this I tried the highlighted section of code below.
View 11 Replies
View Related
Oct 22, 2013
I am trying to open an .xls excel file within a non-email lotus notes database with vba code in excel.
View 3 Replies
View Related
Apr 12, 2011
Am working in Excel 07, but this would need to work in 2000 as well.
Need a macro that will...
1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value.
2. Copy the visible cells
3. Open an email in Outlook (not via the email workbook function of excel), enter "Submission" into the title, enter "Dear X," insert 2 returns.
4. Paste the copied table (not the workbook, just what is on the clipboard) into the body the email.
View 4 Replies
View Related
May 22, 2013
Am trying for a code which will send mail to recipients mentioned in column A [to] Coulmn B [CC] & formatted range should be pasted as body of email lets say C:F. Each time formatted header should remain same is it will contain column heading. I have a code which sends mail to specified recipients mentioned in code .
Code:
Sub Send_Formatted_Range_Data() Dim oWorkSpace As Object, oUIDoc As Object Dim rnBody As Range Dim lnRetVal As Long Dim WB6 As Workbook Set WB6 = Application.Workbooks.Open("C:TESTDaily_BKG.XLS") Const stTo As String =
[Code]....
View 3 Replies
View Related
Apr 15, 2014
We know that professional emails have a fixed format. I have a list of people with their names and emails and there are some people for which emails are missing. I am pasting my sample data below. But first let me explain the meaning of columns.
Column A: Means full name of the person for which we have email id
Column B & C: just split of Available full name into first name and last name
Column D: Email id of the person with full name in column A
Column E: For the names in this column, we need to get email ids
Column H: Sample result
Now basis on the Full Name(Column A) and Email Id(Column D) can we get the email id for person in Column E.
Sheet4
A
B
C
D
E
F
G
H
1
Available Full Name
Available FirstName
Available LastName
Email for available full name
Actual Full name
Actual firstname
Actual lastname
Sample result
[Code] .........
View 2 Replies
View Related
May 12, 2014
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
View 1 Replies
View Related
Dec 7, 2013
I would like to send an email to people if there email address appears in a range. ie if a checkbox = true, then their email address will appear in B2, and the next person in B3 etc... with the code to get the '.To:' to send the email to the addresses that appear in that range.
[Code] ......
View 3 Replies
View Related
