Saquib Ahmad Shuvo
Using mail merge, we can create a collection of documents that each recipient receives individually. If you are looking for some special tricks to mail merge from Excel to Outlook with attachments, you’ve come to the right place. There are numerous ways to mail merge from Excel to Outlook with attachments. This article will discuss two suitable examples to mail merge from Excel to Outlook with attachments. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.
2 Suitable Examples to Mail Merge from Excel to Outlook with Attachments
We will use two effective and tricky ways to mail merge from Excel to Outlook with attachments. This section provides extensive details on the two ways. You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Mail Merge from Excel to Outlook with Single Attachment
Here, we will demonstrate how to mail merge from Excel to Outlook with a single attachment. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing a person’s name, email id, and person-required file.
You can use Visual Basic For Applications (VBA) to mail merge from Excel to Outlook with a single attachment. In order to do this, you need the Developer tab to show on your ribbon. After that, you have to follow the following steps to mail merge from Excel to Outlook.
📌 Steps:
- Firstly, from the ribbon, go to the Developer tab.
- Then, select Visual Basic from the Code group.
- Or, you have to press Alt+F11 to open the VBA editor.
- In the VBA window, go to Insert and select Module.
- Next, you have to type the following code
Sub Single_attachment()
Dim appOutlook As Object
Dim Email As Object
Dim source, mailto As String
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)
mailto = mailto & Cells(2, 2) & ";"
source = "F:\SOFTEKO\61-0055\New folder\" & Cells(2, 3)
Email.attachments.Add source
ThisWorkbook.Save
source = ThisWorkbook.FullName
Email.attachments.Add source
Email.To = mailto
Email.Subject = "Important Sheets"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
Email.Display
End Sub- Afterward, close the Visual Basic window, and press Alt+F8.
- When the Macro dialogue box opens, select Single_attachment in the Macro name. Click on Run.
- As a consequence, an Outlook window will appear, and you will see the desired file attachment
🔎 VBA Code Explanation
Sub Single_attachment()First of all, provide a name for the sub-procedure of the macro.
Dim appOutlook As Object
Dim Email As Object
Dim source, mailto As StringNext, declare the necessary variable for the macro.
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)Here, the set keyword is used to create new objects named as appOutlook and Email.
mailto = mailto & Cells(2, 2) & ";"This variable will contain the person whom to mail.
source = "F:\SOFTEKO\61-0055\New folder\" & Cells(2, 3)This variable will contain the person’s email id from a specified folder.
ThisWorkbook.SaveThis saves common will save the workbook.
source = ThisWorkbook.FullNameThis variable will contain this workbook name.
Email.attachments.Add sourceThis Add method will add a source to a collection object.
Email.To = mailto
Email.Subject = "Important Sheets"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."This variable will return the text “Important sheets” as the email subject and the text “Greetings Everyone, Please go through the sheets, regards as Email body.
Email.DisplayThis Display method will display the Email.
End SubFinally, end the sub-procedure of the macro.
Note:
You need to save the Excel file and docs file in one folder. Otherwise, this method will not work.
Read More: Mail Merge in Excel without Word (2 Suitable Ways)
2. Mail Merge from Excel to Outlook with Multiple Attachments
Here, we will demonstrate how to mail merge from Excel to Outlook with multiple attachments. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing some people’s names, email ids, and person-required files.
You can use Visual Basic For Applications (VBA) to mail merge from Excel to Outlook with multiple attachments. In order to do this, you need the Developer tab to show on your ribbon. After that, you have to follow the following steps to mail merge from Excel
📌 Steps:
- Firstly, from the ribbon, go to the Developer tab.
- Then, select Visual Basic from the Code group.
- Or, you have to press Alt+F11 to open the VBA editor.
- In the VBA window, go to Insert and select Module.
- Next, you have to type the following code
Sub attachments()
Dim appOutlook As Object
Dim Email As Object
Dim source, mailto As String
Dim i, j As Integer
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)
For i = 2 To 5
mailto = mailto & Cells(i, 2) & ";"
Next i
For j = 2 To 5
source = "F:\SOFTEKO\61-0055\New folder\" & Cells(j, 3)
Email.attachments.Add source
Next
ThisWorkbook.Save
source = ThisWorkbook.FullName
Email.attachments.Add source
Email.To = mailto
Email.Subject = "Important Sheets"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
Email.Display
End Sub- Afterward, close the Visual Basic window, and press Alt+F8.
- When the Macro dialogue box opens, select attachments in the Macro name. Click on Run.
- As a result, an Outlook window will appear, and you will see the desired file attachment
🔎 VBA Code Explanation
Sub attachments()First of all, provide a name for the sub-procedure of the macro.
Dim appOutlook As Object
Dim Email As Object
Dim source, mailto As String
Dim i, j As IntegerNext, declare the necessary variable for the macro.
Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)Here, the set keyword is used to create new objects named as appOutlook and Email.
For i = 2 To 5
mailto = mailto & Cells(i, 2) & ";"
Next i
For j = 2 To 5
source = "F:\SOFTEKO\61-0055\New folder\" & Cells(j, 3)After that, starts For Loop. The mailto variable will contain the person whom to mail. The source variable will contain the person’s email id from a specified folder.
NextThen, you have to execute each iteration of the For Loop.
ThisWorkbook.SaveThis save command will save the workbook.
source = ThisWorkbook.FullNameThis variable will contain this workbook name.
Email.attachments.Add sourceThis Add method will add a source to a collection object.
Email.To = mailto
Email.Subject = "Important Sheets"
Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."This variable will return the text “Important sheets” as the email subject and the text “Greetings Everyone, Please go through the sheets, regards as Email body.
Email.DisplayThis Display method will display the Email.
End SubFinally, end the sub-procedure of the macro.
Note:
You need to save the Excel file and docs files in one folder. Otherwise, this method will not work.
Read More: Macro to Populate a Mail Merge Document from Excel
Conclusion
That’s the end of today’s session. I strongly believe that from now, you may be able to mail merge from Excel to Outlook with attachments. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
