+ Reply to Thread
Results 1 to 2 of 2

Creating a macro to generate batch emails from a list of addresses in excel file

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    ireland
    MS-Off Ver
    2010
    Posts
    2

    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

    Sub SendEmails()

    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"
    Case Else
    Msg = "Unknown error"
    End Select
    Msg = "Unable to Send Email to " & vbCrLf & "'" & MailTo & "'" & vbCrLf _
    & vbCrLf & "Error Number " & CStr(RetVal) & vbCrLf _
    & Msg
    RetVal = MsgBox(Msg, vbExclamation + vbOKOnly)
    End If
    Next R

    End Sub

    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.
    Thanks Maccabarra

  2. #2
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need macro that opens .txt file in excel, run macros and save file in batch
    By khalidness in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2014, 11:00 AM
  2. run an excel macro from a batch file
    By Mobho9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2012, 10:31 AM
  3. Sending batch emails via Excel
    By treetop40 in forum Excel General
    Replies: 0
    Last Post: 04-24-2006, 06:25 AM
  4. [SOLVED] Creating an Excel sheet with Addresses using a Macro
    By KnightRiderAW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2006, 05:30 PM
  5. Run Excel Macro through Batch file
    By LJi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1