Sample Code for Merge With Attachments?

[Guest]

I code VBA in Access (intermediate level) and have been going nuts trying to
send emails with PDF attachments to selected recipients. Apparently this can
be done from inside Access, but after weeks of struggle with the appropriate
libraries -- but little helpful advice from experts -- I am giving up. The
PDF creation is a piece of cake. The record selection is easy. The failure
occurs in trying to drive Outlook from within Access.

So, I would like to run an email merge from within Word or Outlook and use
VBA to attach the PDF file. I know how to do a merge using macros but I
don't know how to write the VBA code or where to put it. If someone out
there can show me some sample code and maybe provide a little guidance, I am
sure I can make this work...finally!

Thanks!

 

[Sue Mosher [MVP-Outlook]]

See http://word.mvps.org/faqs/mailmerge/mergewithattachmentscontent.htm for one approach. If you put the code in Outlook's VBA environment (Alt+F11, just like in Access) and derive all Outlook objects from the intrinsic Application object, you won't get security prompts.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

Sue, this is a big leap forward for me on this project. Thank you!

The macro I took from the MVP site seems to work with one exception. When I
step through, this line is dysfunctional:

While Counter <= Maillist.Tables(1).Rows.Count

When I test Maillist... in the Immediate window, I get "The requested member
of the collection does not exist."

What am I missing?

 

[Sue Mosher [MVP-Outlook]]

On the surface, it says that there's no Tables(1) in the MailList document. You can use the Locals window to check the Tables collection.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Sue, this is a big leap forward for me on this project. Thank you!

The macro I took from the MVP site seems to work with one exception. When I
step through, this line is dysfunctional:

While Counter <= Maillist.Tables(1).Rows.Count

When I test Maillist... in the Immediate window, I get "The requested member
of the collection does not exist."

What am I missing?

 

[Guest]

Sue, you were correct. I had failed to set up the list as a table. That is
fixed, and the macro definitely runs -- but no emails go out. I purposely
have not installed "Express ClickYes" yet in order to get confirmation on my
test emails, but no warning messages appear. I'm not sure what to check
next, since I don't see any error messages.

I carefully repeated the MVP instructions for the Directory merge document.
I placed the email address in the 1st column and the file name in the 2nd.
Then I ran the merge to create the MailList, which looks like the sample. I
close both of those, open my message merge document, execute it to a new
document, which produces the 9 test messages. With the 9 messages on screen,
I run the macro. When the FileOpen dialog appears, I select my MailList. I
get a lot of nice, reassuring window flashes, but Outlook (which is running)
doesn't respond.

When I step through the macro, I am a little mystified when this code
appears to execute, even though Outlook is running:

'Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Is that a clue?

Thanks!

 

[Sue Mosher [MVP-Outlook]]

Some anti-virus programs block access to Outlook through CreateObject() and GetObject(). A possible solution is the one I originally suggested -- modify the code to run in Outlook VBA. That should also eliminate the security prompts.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

I'm afraid I need some help doing that. The first bugs I hit are variable
types that don't work in Outlook VBA, and I can't readily see what the
equivalents are.

Thanks.

 

[Sue Mosher [MVP-Outlook]]

Since we can't look over your shoulder, you'll have to give us some clues about the problems you're encountering.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

OK. Here is what I am doing, which I'm sure reflects my naivete with Outlook
and Word macros. I am following the MVP instructions as I understand them.

Word is running. The merge document (with the 9 emails) is open.

Outlook is running. Nothing in Outlook is open. I have copied the macro,
MergeWithAttachments, into Outlook VBA without changing anything. When I try
to compile the macro, these lines produce these errors:

Dim Source As Document: User-defined type not defined.
Dim Maillist As Document: ditto
Dim Datarange As Range: ditto
With Dialogs(wdDialogFileOpen): Sub or Function not defined. ("Dialogs" is
highlighted.)

When I comment-out these lines, the code compiles.

Hope you can clue me in! Thanks.

 

[Sue Mosher [MVP-Outlook]]

Document and Range are Word classes. DId you add a reference to Word to your VBA project?

Dialogs is also a Word class, and you'll need to derive it from a Word.Application object, not Outlook.Application.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

OK. Adding the Word library reference cleared all the compile errors shown
above. Thank you!

Now I am getting new "Method or Data Member not found" compile errors. I
obviously made a mistake in attempting to follow your direction about
deriving Dialogs from a Word.Application object. Here are the lines I think
might be pertinent. The errors occur on all the .Methods:

Dim wdWordApp As Word.Application
Dim wdItem As Word.MailMessage
Set wdWordApp = GetObject(, "Word.Application")

Set wdItem = wdWordApp.MailMessage
With wdItem
' .Subject = mysubject
' .Body = ActiveDocument.Content
Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range
Datarange.End = Datarange.End - 1
' .To = Datarange
For i = 2 To Maillist.Tables(1).Columns.Count
Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range
Datarange.End = Datarange.End - 1
' .Attachments.Add Trim(Datarange.Text), olByValue, 1
Next i
' .Send
End With

Sorry to be so lost on this.

Thanks.

 

[Sue Mosher [MVP-Outlook]]

If Word is not running, GetObject(, "Word.Application") will return Nothing. You must test for that case and use CreateObject if necessary:

On Error Resume Next
Set wdWordApp = GetObject(, "Word.Application")
If wdWordApp Is Nothing Then
Set wdWordApp = CreateObject("Word.Application")
End If
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

I have added your code, but Word has been open all along. GetObject returns
"Microsoft Word."

 

[Sue Mosher [MVP-Outlook]]

Sorry, that was the obvious problem. I should have looked for others, like:

Set wdItem = wdWordApp.MailMessage

You need to use Outlook's Application.CreateItem method to create the message. And

ActiveDocument.Content

This and other calls to Word objects need to use wdWordApp as their parent:

wdWordApp.ActiveDocument.Content

These are good basics to know, BTW, so don't feel like you're wasting time learning them.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers