Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3778
  • Last Modified:

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

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
Amreska
Asked:
Amreska
  • 15
  • 14
  • +2
1 Solution
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
It might be.  Tell me more about what you want to accomplish.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
AmreskaAuthor Commented:
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
 
Stacy BrownCommented:
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
 
AmreskaAuthor Commented:
Hi,

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

Thanks anyway
0
 
David LeeCommented:
Amreska,

Yes, that's possible.  I'll work up some code and post it as soon as I can.
0
 
AmreskaAuthor Commented:
Thank You
0
 
AmreskaAuthor Commented:
Hi BlueDevilFan:

I hope you didn't forget me.

Amreska
0
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
AmreskaAuthor Commented:
Hi BlueDevilFan,

Please let me know if you need more clarifications.

Thanks,
Amreska
0
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
"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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
"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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
"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
 
David LeeCommented:
I should have said with the same subject text.
0
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
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
 
AmreskaAuthor Commented:
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
 
David LeeCommented:
Change line 61 to

    .Subject = "Reminder for Email Change for " & excSht.Cells(lngRow, MM_SUBJECT)
0
 
AmreskaAuthor Commented:
Thank You BlueDeviFan for your patience.  

Amreska
0
 
David LeeCommented:
You're welcome, Amreska.
0
 
bl3nd3rCommented:
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
 
Member_2_7967368Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 15
  • 14
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now