Solved

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

Posted on 2009-07-16
32
2,452 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
ID: 24873078
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
ID: 24873682
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
ID: 24874560
It might be.  Tell me more about what you want to accomplish.
0
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!

 

Author Comment

by:Amreska
ID: 24878545
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
ID: 24879954
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
ID: 24881586
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
ID: 24883556
Amreska,

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

Author Comment

by:Amreska
ID: 24891062
Thank You
0
 

Author Comment

by:Amreska
ID: 24907762
Hi BlueDevilFan:

I hope you didn't forget me.

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24910750
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
ID: 24911154
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
ID: 24919736
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
ID: 24921122
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
ID: 24929057
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
ID: 24929461
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
ID: 24932046
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24933978
"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
ID: 24935709
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
ID: 24936137
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
ID: 24937504
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
ID: 24937621
"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
ID: 24937654
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
ID: 24937761
"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
ID: 24937769
I should have said with the same subject text.
0
 

Author Comment

by:Amreska
ID: 24937893
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
ID: 24938188
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
ID: 24948494
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
ID: 24975947
Change line 61 to

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

Author Comment

by:Amreska
ID: 24975965
Thank You BlueDeviFan for your patience.  

Amreska
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24975995
You're welcome, Amreska.
0
 

Expert Comment

by:bl3nd3r
ID: 25190018
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
ID: 41708051
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Read this checklist to learn more about the 15 things you should never include in an email signature.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

778 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