Solved

A macro to customize the subject and add CC for Email Merge in Word 2003

Posted on 2009-07-16
32
2,219 Views
Last Modified: 2016-07-13
Hi

I am looking for a macro to customize subject line and add CC for Email Merge option in Word 2003.  I use outlook 2007.

Thanks

Amreska
0
Comment
Question by:Amreska
  • 15
  • 14
  • +2
32 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, Amreska.

Mail merges aren't my strong suit.  That said, I don't think this is possible.  Mail merging is built into Word/Outlook.  I don't think there's any opportunity during the merge to run a macro.
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

Is it possible instead of using the mail merge option to have a macro that would incorporate word.  So that it uses word for email merging.

Thanks,

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
It might be.  Tell me more about what you want to accomplish.
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

I would like to mass email.  The emails are custamizable and information is retrieved from excel spreadsheet.  The email subject can also be customized.  I would like to include CC.

The only catch is that I want to incorporate Microsoft Word.  The body of the email will be in Microsoft word and similar to the email merge option except that I can customize the Subject of the email and I can include CC.

Please let me know if this is possible.

Thank You,

Amreska
0
 
LVL 13

Expert Comment

by:Stacy Brown
Comment Utility
I was researching mail merge code on Wednesday and found the code below from this site:  http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.word.vba.beginners&tid=4e361d04-c873-47d2-a9e8-aa7d99a38467&cat=&lang=en&cr=US&sloc=&p=1

Also, this Experts-Exchange site has some code for cc and subject:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_23008555.html

I hope this helps!
Sub SendDocumentAsAttachment() 
 

Dim bStarted As Boolean 

Dim oOutlookApp As Outlook.Application 

Dim oItem As Outlook.MailItem 

Dim FD As FileDialog 

Dim i As Long 
 

On Error Resume Next 
 

If Len(ActiveDocument.Path) = 0 Then 

MsgBox "Document needs to be saved first" 

Exit Sub 

End If 
 

Set oOutlookApp = GetObject(, "Outlook.Application") 

If Err <> 0 Then 

Set oOutlookApp = CreateObject("Outlook.Application") 

bStarted = True 

End If 
 

Set oItem = oOutlookApp.CreateItem(olMailItem) 
 

With oItem 

.To = "recipient@mail.com" 

.Subject = "New subject" 

'Add the document as an attachment, you can use the .displayname 

property 

'to set the description that's used in the message 

.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _ 

DisplayName:="Document as attachment" 

Set FD = Application.FileDialog(msoFileDialogOpen) 

With FD 

.AllowMultiSelect = True 

If .Show = -1 Then 

For i = 1 To .SelectedItems.Count 

oItem.Attachments.Add .SelectedItems(i) 

Next i 

End If 

End With 

Set FD = Nothing 

.Send 

End With 
 

If bStarted Then 

oOutlookApp.Quit 

End If 
 

Set oItem = Nothing 

Set oOutlookApp = Nothing 
 

End Sub 

Open in new window

0
 

Author Comment

by:Amreska
Comment Utility
Hi,

Still not what I am looking for.  Because I am looking for mass emailing.

Thanks anyway
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Amreska,

Yes, that's possible.  I'll work up some code and post it as soon as I can.
0
 

Author Comment

by:Amreska
Comment Utility
Thank You
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

I hope you didn't forget me.

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hey, Amreska.

No, I haven't forgotten.  I'm just slow sometimes.

Let me make sure I understand.  All messages will have the same body from Word.  The only difference in each message will be the subject line and CC address.  Is that right?
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

The messages in Word can be customized for each email.  So that the custom words for each email will receive information from excel.  

For example,

The body will have

 Hello "Name"

If excel has a column named "Name" and each email will have a name based on the rows of the spreadsheet.

So one email will have Hello James, next email will have Hello Michael, an so forth.
In addition, I can customize the subject based on information  from excel and I can add CC.

Thanks,

Amreska
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan,

Please let me know if you need more clarifications.

Thanks,
Amreska
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Ok, here's my solution.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

This solution makes the following assumptions.

1.  The merge document is a standard Word merge document.
2.  All merge data, including the recipient's address, is in an Excel spreadsheet.
3.  The spreadsheet is laid out one row per recipient.
Sub RunMerge()

    'Change the spreadsheet columns that the data is in on the next four lines'

    Const MM_ADDRESS = "A"

    Const MM_CC = "B"

    Const MM_SUBJECT = "C"

    Const MM_GREETING = "D"

    Const wdFormatOriginalFormatting = 16

    

    Dim excApp As Object, _

        excBook As Object, _

        excSht As Object

    Dim wrdApp As Object, _

        wrdDoc As Object, _

        wrdRng As Object, _

        wrdFld As Object, _

        wrdSel As Object

    Dim olkMsg As Object, _

        olkIns As Object, _

        olkDoc As Object, _

        olkSel As Object

    Dim lngRow As Long

    

    'Initialize Excel'

    Set excApp = CreateObject("Excel.Application")

    'Change the file name and path on the next line'

    Set excBook = excApp.Workbooks.Open("C:\eeTesting\Merge Spreadsheet.xls")

    'Change the worksheet number on the next line'

    Set excSht = excBook.Worksheets(1)

    

    'Initialize Word'

    Set wrdApp = CreateObject("Word.Application")

    

    'Merge'

    lngRow = 1

    Do Until excSht.Cells(lngRow, MM_ADDRESS) = ""

        Set olkMsg = Outlook.Application.CreateItem(olMailItem)

        olkMsg.Body = ""

        Set olkIns = olkMsg.GetInspector

        Set olkDoc = olkIns.WordEditor

        'Change the file name and path on the next line'

        Set wrdDoc = wrdApp.Documents.Open("C:\eeTesting\Merge Document.doc")

        For Each wrdRng In wrdDoc.StoryRanges

            For Each wrdFld In wrdRng.Fields

                Select Case wrdFld.Result.Text

                    'Change the merge field name on the next line.  Leave the arrows.'

                    Case "«Greeting»"

                        wrdFld.Result.Text = excSht.Cells(lngRow, MM_GREETING)

                End Select

            Next

        Next

        Set wrdRng = wrdDoc.StoryRanges(1)

        wrdRng.Select

        Set wrdSel = wrdApp.Selection

        wrdSel.Copy

        olkDoc.Windows(1).Document.Range(0, olkDoc.Characters.count).Select

        Set olkSel = olkDoc.Windows(1).Selection

        olkSel.PasteAndFormat Type:=wdFormatOriginalFormatting

        With olkMsg

            .To = excSht.Cells(lngRow, MM_ADDRESS)

            .CC = excSht.Cells(lngRow, MM_CC)

            .Subject = excSht.Cells(lngRow, MM_SUBJECT)

            .Display

        End With

        Set olkIns = Nothing

        Set olkDoc = Nothing

        wrdDoc.Close False

        Set wrdDoc = Nothing

        lngRow = lngRow + 1

    Loop

    Set excSht = Nothing

    excBook.Close False

    Set excBook = Nothing

    excApp.Quit

    Set excApp = Nothing

    wrdApp.Quit False

    Set wrdApp = Nothing

    Set wrdRng = Nothing

    Set wrdFld = Nothing

    Set wrdSel = Nothing

    Set olkMsg = Nothing

    Set olkRcp = Nothing

    Set olkDoc = Nothing

    Set olkSel = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan,

Thank for your help.

It seems that some emails turn out to be blank; although, I have text in word.  Some are blank are others are fine.  Is there a reason for that?

Thanks again,

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Not that i know of.  I'd have to see what's happening in order to figure that out.  Is it possible for you to upload the Word document and a sample of the spreadsheet your using so I can test here?
0
 

Author Comment

by:Amreska
Comment Utility
Hi BluDevilFan:

It works fine now.  I think its a memory issue.  One more thing.  

I would like to also edit the subject in the maco.  For example, I want to have customized text from excel in the macro.  For example, in the maco I have the subject of Hello <<Name>>, and <<Name>> is retrieved from excel.


Thanks,

Amreska
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

Expert Comment

by:David Lee
Comment Utility
"would like to also edit the subject in the maco"
Sorry, I don't understand.  The solution already includes inserting the subject.  That's done on line 61.  It also includes the ability to insert a custom greeting on lines 46 - 47.  That greeting can be the person's name.
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

I believe the macro at hand allows only copying the subject from excel.  I want to be able to customize the subject.  Because the subject can change based on my needs.  I want to customize the subject like I can customize the word document.

Thanks,

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I guess I don't understand what you mean by "customize the subject".  Right now the subject is being read in from the Excel file.  As I described above the Excel file is presumed to have one recipient per line.  Something like this

Joe@company.com    Tom@somewhere.com    Some Subject      Hi, Joe
Bill@someplace.com  Sally@anywhere.com      Another subject   Greetings, Bill

The script reads the subject on a per message basis.  The message to Joe will have a subject of "Some Subject" while the message to Bill will have a subject of "Another Subject".  Isn't that what you wanted?  If that's not what you want, then please explain how you want to customize the subject.
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

I want to also add text to Hi, Joe and Greetings, Bill  that is extracted from excel  So that I want to say in the Macro:

"This is Greeting for Hi, Joe" and "This is Greeting for Greetings, Bil".  I want to have my own text plus from excel.  Or I may have a subject that says" This is email for Joe@company.com"

So I have Subject = This is email for <<ColumnA>>, and the subject would go through each row.  So that it becomes This is email for "row1", This is email for "row2", This is email for "row3", and so forth.

row 1, row2, and row3 can be any email I input in excel.

Hope that is more clear.

Thanks,

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
"So I have Subject = This is email for <<ColumnA>>, and the subject would go through each row.  So that it becomes This is email for "row1", This is email for "row2", This is email for "row3", and so forth."

That's exactly what you have now.  Each row contains the recipient's address, the CC address, the subject to be used, and the greeting.  I must be missing something.
0
 

Author Comment

by:Amreska
Comment Utility
So how  do I add the text for the subject incase I want to have one subject that goestt o multiple people.  Excel will have the changing part of the subject from one email to the next, but the text I add in the Macro will stay the same from one email to the next.

0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
"So how  do I add the text for the subject incase I want to have one subject that goestt o multiple people."
Use the same subject on those lines.  Using the same example I used above

Joe@company.com    Tom@somewhere.com    Some Subject Text Goes Here      Hi, Joe
Bill@someplace.com  Sally@anywhere.com      Some Subject Text Goes Here      Greetings, Bill

Now Joe and Bill will receive messages with the same text.  
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I should have said with the same subject text.
0
 

Author Comment

by:Amreska
Comment Utility
BlueDevilFan:

Thank you for your patience.  Now lets forget about excel having a subject column.  Lets say I want to customize the subject from other columns in Excel.  Lets Say, I have column named "Name"

I type Hello "Name", then Name will go through each row.  So I have Hello James, Hello Michael, etc.

Thank You Again,
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I must not being doing a good job of explaining how this works.  Let me try it another way.  The solution assumes that the spreadsheet contains one row for each recipient.  If the merge is going to 100 people, then the spreadsheet would have 100 rows.  Each row is composed of four columns.  One column for the address of the recipient; one column for the address (if any) of a person you want to CC; one column for the subject; one column for a greeting.  Something like this

               Column A                                           Column B                                                 Column C                                                       Column D
1     Address of Recipient #1     Address of CC Recipient for Recipient #1     Subject Line to be Used for Recipient #1     Greeting to be Used For Recipient #1
2     Address of Recipient #2     Address of CC Recipient for Recipient #2     Subject Line to be Used for Recipient #2     Greeting to be Used For Recipient #2
3     Address of Recipient #3     Address of CC Recipient for Recipient #3     Subject Line to be Used for Recipient #3     Greeting to be Used For Recipient #3

If I want recipient #1's greeting to be "Hello, (Recipient #1's Name)" then I enter that in column D of row 1.  If I want to use the same greeting for recipient #2, then I enter "Hello, (Recipient #2's Name)" in column D of row 2.  Does that make sense?

If instead I want all messages to begin with "Hello" and I just need to fill in the name, then I create the merge document with the literal text "Hello, " and insert a merge field after "Hello".  I then modify the code to use the merge field's name and then in column D of each row all I have to enter is the person's name.  Does that make sense?  

I'm wondering if you understood what I meant in the post with the code when I said, "1.  The merge document is a standard Word merge document." under "Assumptions".  Merge documents have merge fields where you want to insert different data for each message.  Does your merge document have merge fields in it?  If "no", then that's the problem.  If yes, then did you edit the code per the comments I included in it about changing the merge field name?  Specifically, lines 45 (instructions) and 46 (code that needs changing).  If your document does have a merge field and you didn't change the name in code, then that's the problem.
0
 

Author Comment

by:Amreska
Comment Utility
Hi BlueDevilFan:

One more question.  On line 61: .Subject = excSht.Cells(lngRow, MM_SUBJECT), how do I put instead:

.Subject = Reminder for Email Change for excSht.Cells(lngRow, MM_SUBJECT).

So that I have my text + info from excel spreadsheet (excSht.Cells(lngRow, MM_SUBJECT)I.

Thanks,
Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Change line 61 to

    .Subject = "Reminder for Email Change for " & excSht.Cells(lngRow, MM_SUBJECT)
0
 

Author Comment

by:Amreska
Comment Utility
Thank You BlueDeviFan for your patience.  

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome, Amreska.
0
 

Expert Comment

by:bl3nd3r
Comment Utility
BlueDevilFan, is it possible for this code to send the email completely? When I run it, it only creates the message but doesn't send it. Thank you for your help.
0
 

Expert Comment

by:Member_2_7967368
Comment Utility
Hi,

I am trying to run this code.
Outlook is getting hung. I am a new in this but should I create MailMerge Document in Word and then run the Macro from Outlook? Also my other question is- are there headers in the excel sheet? Thanks in advance.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now