Mail Merge Dynamic Subject Change with Email

[agardin]

Hi,

I am trying to do a mail merge from Excel 2003 to Word 2003. I have the document all done and it all syncs there. I then want to email it out and I want to make the subject read from an excel column.

One example is have an invoice number in the subject line of an email. And for each email or invoice the invoice number in the Subject line changes. My case does not use a number but just words.

I have been looking for this all day and have found out that this can be done with VB. I found the following VB progam on another site but I could not get it to work. Any help would be great.





Dim WithEvents wdapp As Application
Dim EMAIL_SUBJECT As String
Dim FIRST_RECORD As Boolean


Private Sub Document_Open()

Set wdapp = Application
ThisDocument.MailMerge.ShowWizard 1

End Sub

Private Sub Document_Close()

Set wdapp = Nothing

End Sub


Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
Dim i As Integer

With ActiveDocument.MailMerge

If FIRST_RECORD = True Then
EMAIL_SUBJECT = .MailSubject
FIRST_RECORD = False
Else .MailSubject = EMAIL_SUBJECT
End If

i = .DataSource.DataFields.Count

Do While i > 0
.MailSubject = Replace(.MailSubject, "<" & .DataSource.DataFields(i).Name & ">", .DataSource.DataFields(i).Value, , , vbTextCompare)
i = i - 1
Loop

End With


End Sub


Private Sub wdapp_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)

FIRST_RECORD = True

End Sub

Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)

ActiveDocument.MailMerge.MailSubject = EMAIL_SUBJECT

End Sub

 

[John_w]

That looks like the same code as http://www.access-programmers.co.uk/forums/showthread.php?t=153216. Follow the steps discussed there about how to make it work, in particular:

1. Paste the code in the ThisDocument module in your Word document.

2. Set the macro security of the Word document to Medium; or High and apply a digital signature to the VBA project.

3. Save, close and reopen the Word document.

 

[agardin]

That is the site that I have been trying to follow. I just went through the steps another time and it returned with a pop up window to send an error to microsoft.

"Microsoft Office Word has encountered a problem and needs to close. We are sorry for the inconvenience."

Does it matter if the email is HTML, Text only, or attachment?

 

[John_w]

What versions of Word and Excel are you using?

The code works for me on 2003 in HTML format and plain text (but with a warning saying 'All macros in this document will be lost if the document is saved in Plain Text. Do you want to save in this format anyway?' - click Yes). I didn't try Attachment. It only worked after I'd changed macro security settings in the Word document from High to Medium (and clicked Enable Macros when opening the document). It also worked with High and the VBA project digitally signed.

 

[agardin]

I have gotten this to merge and email on 2007 with a test file. The test file had maybe 4 columns and it sent and merged fine.

I then tried to do it with my full spreadsheet and I recieved the same error as before.

What might be the difference between the two files? Is there some sort of formatting thing that I need to change?

Thank you for all of your help.

 

[John_w]

My test used 3 columns.

You might need a bit of trial and error to discover why yours doesn't work. Try adding one column at a time to the merge and see when it fails.

 

[agardin]

Well I got it to work finally. I had to cut my columns down to only AH or 34 columns. I had columns to about CA but slowly starting making cut of the data that was not needed in the merge but are from the file.

I guess this will be good to know for future people. If the file is not merging then you need to make your file have fewer columns.

Thanks again for all of the help.

 

[lovesid]

Wow... Thank you so much for this solution ,worked like a charm ....expecting a bit more ,my requirement to complete mail merge from a shared mailbox.

I can create a separate profile but can we do so programmatic way by any chance.


Thanks for this