Creating mailmerge from excel using VBA

jdhfch

Board Regular
Joined
Jan 25, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hi there,

Please can someone help me?

I have created (found on google) a code to create amailmerge from excel using a Macro. I am an absolute novice with VBA so pleasego slow!

The code is:

Dim Outlook As Object
Dim Email As Object
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim address As Range, rngCell As Range
Dim Recipients As String
Dim Lr As Integer
Dim i As Integer
Dim msg As String
Set ws = Sheets("Data") ' Your data sheet.
Set ws1 = Sheets("MailMerge")
Lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
Set Outlook = CreateObject("Outlook.Application")
Set Email = Outlook.CreateItem(0)

msg = ""
msg = msg & "Dear " &Christian_Name_for_Mail_Merge & "," & vbCrLf & vbCrLf

msg = msg & "I would like to send you an update(blah, blah!)." & vbCrLf & vbCrLf

msg = msg & "The following is an overview of your progress(blah, blah):" & vbCrLf & vbCrLf

msg = msg & "Account Number: " & Account_Number & vbCrLf& vbCrLf
msg = msg & "Account Name: " & Account_Name & vbCrLf& vbCrLf
msg = msg & "Target: " & Target & vbCrLf& vbCrLf
msg = msg & "Turnover to date: " & Turnover_To_Date & vbCrLf& vbCrLf
msg = msg & "Remainder to do " & To_Do & vbCrLf &vbCrLf


msg = msg & "I will continue to update you on amore regular basis, as the end of the period approaches" & vbCrLf& vbCrLf

msg = msg & "Kind Regards" & vbCrLf &vbCrLf
Email.Importance = 2
Email.Subject = "Update"
Email.Body = msg
'Email.Attachments.Add ActiveWorkbook.FullName
'Set Recipient
Email.To = Email_for_Mail_Merge
Email.Send

End If
Next
MsgBox "Email Sent Successfully"
End Sub


Sub MailMerge()
End Sub

When I run this it errors on line 10 Set ws =Sheets("ABMVersion") ' Your data sheet. With the error “ComplieError: invalid outside procedure”

Please can anyone help with this and any other potentialerrors/Improvements?

 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Looks like Sub MailMerge is in the wrong place.:)

Try this.
Code:
Sub MailMerge()
Dim Outlook As Object
Dim Email As Object
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim address As Range, rngCell As Range
Dim Recipients As String
Dim Lr As Long
Dim i As Long
Dim msg As String

    Set ws = Sheets("Data") ' Your data sheet.
    
    Set ws1 = Sheets("MailMerge")
    
    Lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To Lr
    
        Set Outlook = CreateObject("Outlook.Application")
        Set Email = Outlook.CreateItem(0)
        
        msg = ""
        msg = msg & "Dear " & Christian_Name_for_Mail_Merge & "," & vbCrLf & vbCrLf
        
        msg = msg & "I would like to send you an update(blah, blah!)." & vbCrLf & vbCrLf
        
        msg = msg & "The following is an overview of your progress(blah, blah):" & vbCrLf & vbCrLf
        
        msg = msg & "Account Number: " & Account_Number & vbCrLf & vbCrLf
        msg = msg & "Account Name: " & Account_Name & vbCrLf & vbCrLf
        msg = msg & "Target: " & Target & vbCrLf & vbCrLf
        msg = msg & "Turnover to date: " & Turnover_To_Date & vbCrLf & vbCrLf
        msg = msg & "Remainder to do " & To_Do & vbCrLf & vbCrLf
        
        
        msg = msg & "I will continue to update you on amore regular basis, as the end of the period approaches" & vbCrLf & vbCrLf
        
        msg = msg & "Kind Regards" & vbCrLf & vbCrLf
        Email.Importance = 2
        Email.Subject = "Update"
        Email.Body = msg
        'Email.Attachments.Add ActiveWorkbook.FullName
        'Set Recipient
        Email.To = Email_for_Mail_Merge
        Email.Send
        
        End If
        
    Next i
    
    MsgBox "Email Sent Successfully"
    
End Sub
 
Upvote 0
Thank you for helping.

It has now moved on, but I am now getting error Compile Error End if without block if

I am using

Sub MailMerge()
Dim Outlook As Object
Dim Email As Object
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim address As Range, rngCell As Range
Dim Recipients As String
Dim Lr As Integer
Dim i As Integer
Dim msg As String

Set ws =Sheets("Data") ' Your data sheet.

Set ws1 =Sheets("MailMerge")

Lr =ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lr

Set Outlook =CreateObject("Outlook.Application")
Set Email =Outlook.CreateItem(0)

msg =""
msg = msg & "Dear " &Christian_Name_for_Mail_Merge & "," & vbCrLf & vbCrLf

msg = msg& " I would like to send you an update(blah, blah!)." &vbCrLf & vbCrLf

msg = msg& " The following is an overview of your progress(blah, blah):"& vbCrLf & vbCrLf

msg = msg& "Account Number: "& Account_Number & vbCrLf & vbCrLf
msg = msg& "Account Name: "& Account_Name & vbCrLf & vbCrLf
msg = msg& "Target: "& Target & vbCrLf & vbCrLf
msg = msg& "Turnover to date: "& Turnover_To_Date_ABM_update_with_Vlookup & vbCrLf & vbCrLf
msg = msg& "Remainder to do "& To_Do & vbCrLf & vbCrLf


msg = msg& "I will continue to update you on a more regular basis, as the endof the period approaches" & vbCrLf & vbCrLf

msg = msg& "Kind Regards" & vbCrLf & vbCrLf
Email.Importance = 2
Email.Subject= "Update"
Email.Body =msg
'Email.Attachments.Add ActiveWorkbook.FullName
'Set Recipient
Email.To =Email_for_Mail_Merge
Email.Send

End If

Next i

MsgBox "EmailSent Successfully"
End Sub
 
Upvote 0
I think you might need to go back to the source as it's not clear where the missing 'If' should go, or what it's checking for.

Also, some of that code isn't quite right anyway.

For example, you shouldn't be creating a new instance of Outlook every time you want to send an email, which is what's happening here.
Code:
For i = 2 To Lr

Set Outlook =CreateObject("Outlook.Application")
Set Email =Outlook.CreateItem(0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top