Creating a macro to generate batch emails from a list of addresses in excel file
Hi I have this macro to create email from a list of addresses I have in an excel file. So far I can create the email with a subject title.
Private Declare Function ShellExecute _
Lib "Shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Dim Msg As String
Dim R As Long
Dim RetVal As Long
Dim Subj As String
Dim URL As String
StartRow = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
For R = StartRow To LastRow
Subj = "Dear " & Cells(R, "A").Text & ", I wanted to let you know that you will receive " & Cells(R, "C").Text
URL = "MailTo:" & Cells(R, "B").Text & "?subject=" & Subj
RetVal = ShellExecute(0&, "open", URL, vbNullString, vbNullStirng, 0&)
'Did Connection Fail? Errors are from 0 to 32
If RetVal <= 32 Then
Select Case RetVal
Case 2 'SE_ERR_FNF
Msg = "File not found"
Case 3 'SE_ERR_PNF
Msg = "Path not found"
Case 5 'SE_ERR_ACCESSDENIED
Msg = "Access denied"
Case 8 'SE_ERR_OOM
Msg = "Out of memory"
Case 32 'SE_ERR_DLLNOTFOUND
Msg = "DLL not found"
Case 26 'SE_ERR_SHARE
Msg = "A sharing violation occurred"
Case 27 'SE_ERR_ASSOCINCOMPLETE
Msg = "Incomplete or invalid file association"
Case 28 'SE_ERR_DDETIMEOUT
Msg = "DDE Time out"
Case 29 'SE_ERR_DDEFAIL
Msg = "DDE transaction failed"
Case 30 'SE_ERR_DDEBUSY
Msg = "DDE busy"
Case 31 'SE_ERR_NOASSOC
Msg = "Default Email not configured"
Case 11 'ERROR_BAD_FORMAT
Msg = "Invalid EXE file or error in EXE image"
Msg = "Unknown error"
Msg = "Unable to Send Email to " & vbCrLf & "'" & MailTo & "'" & vbCrLf _
& vbCrLf & "Error Number " & CStr(RetVal) & vbCrLf _
RetVal = MsgBox(Msg, vbExclamation + vbOKOnly)
1. When I Run the macro, the email generates in Outlook as expected but do i have to hit the 'Send' button each time manually? I have a very large list of emails that I will be generating, is there any way to add function to auto send the mail as well as soon as you run the macro?
2. I am trying to add in the email some text, instead of just have the info on the subject line, but I can't seem to get it to work. I have tried adding 'Body= ' etc and variations of this but the macro throws up an error each time. Ideally I want to add in a line of text and then also if possible pull a table/data directly from the excel worksheet and show it in the body of the mail.
Any help would be great from anyone.
Re: Creating a macro to generate batch emails from a list of addresses in excel file
I would be interested in a solution to this as I am using the same script and it works great if you are not sending a lot of mails.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By khalidness in forum Excel Programming / VBA / Macros
Last Post: 11-11-2014, 11:00 AM
By Mobho9 in forum Excel Programming / VBA / Macros
Last Post: 10-15-2012, 10:31 AM
By treetop40 in forum Excel General
Last Post: 04-24-2006, 06:25 AM
By KnightRiderAW in forum Excel Programming / VBA / Macros
Last Post: 01-11-2006, 05:30 PM
By LJi in forum Excel Programming / VBA / Macros
Last Post: 08-03-2005, 02:05 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1