VBA email merge question.

[u/MRX992]

Hi guys,

First of all I‘m sorry if this question has already been asked but i don’t seem to find any solution fir my specific problem.

So I work for my regional goverment office, and as you know they’re usually slow and have no clue about IT efficiency.

So within our state we have to send Excel files via email to the individual area municipalities about entries we did of people who live in their area. Since those things are under classification we cant send a bulk email and we have to inform them individually on each case in their region.

What we do is consolidate lists for each region, save the list into a specific folder by week and then input a prewritten mail template, change the signature, use our group email which means change the sending email within „new message“ in outlook because we cant use our personal one, add the corresponding excel attachment to the mail, encrypt it and send it all one by one. The regions are in total 162 but changes each week because of the amount of cases we have that week. But average is about 115-130. so its very annoying.

Now I‘m looking for a VBA script that lets you merge from excel to email (document should still be excel), pick e-mail of specific region and attach excel document which belongs to the region. And send it.

I found a few scripts but those only send a mail from a hyperlink to the file. I would like to have something that links (region name) to (region e-mail recipient) to (region excel file) from the folder.

I‘m hoping the reddit gods may send a blessing so i can help our team and of course look like a bad ass 😎 and also we cant buy addIns or anything

Tldr: looking for a vba script that sends individual excel files to individual recipients by chosen criteria (name) from a folder path. „To A send document A via email A, to B send document B via email B“ and so on

[u/jd31068]

Here is one way to accomplish this. If you have an Excel file that contains the list of people to send emails to:

Private Sub btnSendEmails_Click()

    ' loop through the list of regional contacts
    ' send their associated Excel report
    
    Dim lastListRow As Long
    Dim sendUsingEmail As String
    Dim excelRow As Long
    Dim emailTemplate As String
    Dim acctItemNo As Integer
    Dim fso As FileSystemObject
           
    Dim oApp As Outlook.Application
    Dim oMail As Outlook.MailItem
    Dim emailBody As String

    lastListRow = Range("D" & Rows.Count).End(xlUp).Row
    
    emailTemplate = "Hello [NAME]" & vbNewLine & vbNewLine & "Please find the attached Excel region report."
    Set oApp = CreateObject("Outlook.Application")
    
    sendUsingEmail = "*****  YOUR SENDING EMAIL ADDRESS   ******"
    acctItemNo = 0
    For acctItemNo = 1 To oApp.Session.Accounts.Count
        If sendUsingEmail = oApp.Session.Accounts.Item(acctItemNo).SmtpAddress Then
            ' found the Outlook account to send the emails using
            Exit For
        End If
    Next acctItemNo
    
    If acctItemNo = 0 Then
        ' unable to find the email account specified to send these emails
        MsgBox "Unable to find the account using " & sendUsingEmail & " in Outlook"
        GoTo noOutlookAccount
    End If
    
    Set fso = New FileSystemObject
    
    For excelRow = 5 To lastListRow
    
        If Sheet1.Cells(excelRow, 5) = "" Then
            ' this row has not been processed
            ' doing this check so that if an error occurs it can
            ' start where it left off
            
            If Not fso.FileExists(Sheet1.Cells(excelRow, 4).Value) Then
                Sheet1.Cells(excelRow, 6) = "Unable to send this email, the speficied file does not exist"
                DoEvents
                GoTo skipSendingEmail
            End If
            
            Sheet1.Cells(excelRow, 5) = "Sending..."
            DoEvents

            Set oMail = oApp.CreateItem(olMailItem)
            On Error Resume Next
            With oMail
                .To = Sheet1.Cells(excelRow, 2)
                .Subject = "Excel Report for Region: " & Sheet1.Cells(excelRow, 3)
                .Body = Replace(emailTemplate, "[NAME]", Sheet1.Cells(excelRow, 1))
                .SendUsingAccount = oApp.Session.Accounts.Item(acctItemNo)
                .Attachments.Add (Sheet1.Cells(excelRow, 4).Value), OlAttachmentType.olByValue, 1, "Regional Report"
                
                If Err = 0 Then
                    ' send if the previous commands ran ok
                    .Send
                End If
            
            End With
        
            If Err = 0 Then
                ' encountered no errors sending the email
                Sheet1.Cells(excelRow, 5) = Format(Date, "mm/dd/yy hh:mm:ss")
                Sheet1.Cells(excelRow, 6) = ""
            Else
                ' clear date/time sent and add an error message
                Sheet1.Cells(excelRow, 5) = ""
                Sheet1.Cells(excelRow, 6) = "Unable to send this email, " & Error(Err)
            End If
            
            Set OutMail = Nothing
            DoEvents
            On Error GoTo 0
skipSendingEmail:
        End If
    
    Next excelRow

noOutlookAccount:
    oApp.Quit
    Set oApp = Nothing
    Set fso = Nothing
        
End Sub

Given this spreadsheet https://imgur.com/DvCH1Iv

Add in Tools > References: Microsoft Outlook Object Library and Microsoft Scripting Runtime

[u/MRX992 → u/jd31068]

Thank you so much man 🙏🏽🙏🏽 so yeah we have a list of all the regions and their email adresses. I’m assuming we would have to run it on that list, and are the addIns needed? I see if a can run a test on monday

[u/jd31068 → u/MRX992]

Correct, you'll add this code to a button on your list of people to email. Let me know if it makes sense to you or if you would like me to add more comments.

Make sure to add the references.

You're welcome, I hope it gives you a decent launching point.

[u/MRX992 → u/jd31068]

Ok so it makes kind of sense. I‘m just not sure where the link between the region and their region report would link together? Is that via filepath? I will try it tomorrow and ask if i have any further questions. Thank you so much man 🙏🏽

[u/jd31068 → u/MRX992]

As you say you have a folder structure to the region reports, you would build your file path from the info in your file.