By the end of this article, you'll have answers to these questions:
- How to write vba code for mail merge feature?
- How to add cc and bcc while using mail merge?
- How to add attachments during mail merge?
- How to send email using vba?
If you are not new to Microsoft Word, then I'm sure that you must have heard about mail merge. This allow you to send a personalized email to each recipient. It uses a common template, merges user specific parameters and sends email.
A problem grabbed my attention when a person asked me if he can add his email id in cc of each mail he trigger using mail-merge. Default mail-merge functionality does not allow you to add attachments or cc/bcc to other recipients.
Though, an alternate is available.
You usually start with a document like the one shown below
and a data source (excel)
STEPS:
1. Open VBA Editor in Ms Word(Alt+F11). Insert a new module.
2. Copy-paste this code there.
Sub xtremeexcel()
Dim xlapp As New Excel.Application
Dim xl As Excel.Workbook
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
'Update path of source file(excel)
Set xl = xlapp.Workbooks.Open("C:\Users\abc\Desktop\Mail Merge\data.xls")
On Error Resume Next
For xcount = 2 To xl.Sheets(1).Range("A:A").Find(what:="*", Searchdirection:=xlprevious).Row
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject(olMailItem)
End If
With oItem
.To = xl.Sheets(1).Cells(xcocunt, 5).Value
.CC = "excel.microk@gmail.com"
.Subject = "Extreme Excel Solutions"
.Body = ActiveDocument.Content
For x = 1 To 4
.Body = Replace(.Body, "_" & x & "_", xl.Sheets(1).Cells(xlcount, x).Value)
Next
.send
End With
Set oItem = Nothing
Set oOutlookApp = Nothing
Next
xl.Close
Set xl = Nothing
End Sub
3. Go to Tools >> Reference and make sure these libraries are included.
4. Run this macro. And this is it.. :-)
xTremeExcel
Website : http://www.extremeexcelsolutions.weebly.com
Youtube : https://www.youtube.com/user/xtremeExcel
Email : excel.microk@gmail.com
