Automatically mail a word document 1

[LonnieJohnson]

I have managed to create a macro within a word document that automatically does a mail merge from an MS Access datasource. As a final or additional step, I would like for this document (labels) to automatically be mailed to a predetermined recepiant. I tried to do this in access, but the code I built in access ran before the label macro was done.

My question:

Can I pause the access code or can I do the mail send in word?

Any guidence would be appreciated. I am not that well versed on VBA or macros in word.

 

[grannyM]

You might try the code below. You can have a set recipient or you can put a variable in the parenthesis instead of an email address in quotes. You can also replace the .send with .display to create an open email that can be sent after the macro has finished. (helps when testing)

Make sure you turn on the MS Outlook 9.0 object Library (or current version) in Tools/References

Dim olapp As Outlook.Application
Dim nspNamespace As Outlook.NameSpace
Dim objNewMail As Outlook.MailItem

Set olapp = New Outlook.Application
Set nspNamespace = olapp.GetNamespace("Mapi")
Set objNewMail = olapp.CreateItem(olMailItem)

    With objNewMail
        .Recipients.Add ("email address")
        .Subject = "email subject line"
        .Body = "body text"
        .Attachments.Add ("F:\filename.doc")
        .Send
    End With

Set nspNameSpace = Nothing
Set objNewMail = Nothing

 

[LonnieJohnson]

Thanks granny,

I have simular code. My problem is that it runs before the macro ends. The macro that runs to build the labels is in a word document. I am doing this because I need the bar code. Access dosen't give me that. So anyway I open a word document from access and run a macro that creates another label document. Seems like a lot of work. There may be an easier way. I have the send mail code above in my access code and it runs before the macro in word can build the document. lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[grannyM]

Sorry, I'm a little confused. I have this code in Word, not Access. I'm reading that you have it in access. If that's true, you might try it in word. If I'm reading it wrong, sorry for the confusion.

 

[Hasse]

Can't help you except, maybe:
- recently, I remember having seen a procedure that waits untill a certain (any used) process is finished. Unfortunately I didn't bookmark it, but maybe, if you try mvps/google/...
- if you create and store the result in Word, have you already tried to incorporate a loop in your code which waits (maybe a limited amount of time, for security's sake) until the file is created (you can check the existence of a file using dir())? Something like
"do while not(dir(...) = ...) or LimitedAmount£OfTimeTimer out
...
loop"
Hasse

 

[LonnieJohnson]

Sorry grannyM for the confusion.

I have a VBA code in Access that opens a word document and and then runs a macro in the document that creates another label document in word.

Here is the code:

Shell ("C:\Program Files\Microsoft Office\Office\WINWORD.EXE " _
& "I:\DEPTDATA\WVLBS\LBL_PREP_WV.doc /MMakeLabels&quot, vbMaximizedFocus

The word document opens and a the MMakeLables macro that is in the LBL_PREP_WV.doc creates a label document.

What I'd like to do is mail the end result.

I put another line of code in the Access VBA right after the Shell command to mail the file, but it runs before the word macro is done.

Can I put a step in Word to mail itself as soon as it is done creating the labels?

I know nothing about coding VBA in Word.

Thanks agian. I hope that's clearer.

lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[grannyM]

yes, you can put the code I gave you as a function at the bottom of your word macro(after the End Sub), then just call the function at the end of your word macro before the End Sub. It would look something like this:

existing macro code . . .
EmailNotification
End Sub

Function EmailNotification()

Dim olapp As Outlook.Application
Dim nspNamespace As Outlook.NameSpace
Dim objNewMail As Outlook.MailItem

Set olapp = New Outlook.Application
Set nspNamespace = olapp.GetNamespace("Mapi")
Set objNewMail = olapp.CreateItem(olMailItem)

    With objNewMail
        .Recipients.Add ("email address")
        .Subject = "email subject line"
        .Body = "body text"
        .Attachments.Add ("F:\filename.doc")
        .Send
    End With

Set nspNameSpace = Nothing
Set objNewMail = Nothing

End Function

Make sure you turn on the MS Outlook 9.0 object Library (or current version) in Tools/References in your Word VBA.

One drawback is that you must save the Word Document first in order to attach it to the email. However, you can use a standard filename and just save over it each time you run the macro. If you were just sending an email with a specific text in the body of the email, you could leave the attachment line off. In order to save the word document the code would be:

savename$ = "path\filename"
ActiveDocument.SaveAs Filename:=savename$, fileformat:=wdFormatDocument

This Code would go between the existing word code and the EMailNotification line.

Hope this helps. I'm fairly new at VBA also and have only used VBA for Excel and Word, but this notification email is something we use a lot.

 

[LonnieJohnson]

That's what I'm looking for. Thanks a bunch. I'll let you know how it works. lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[LonnieJohnson]

I tried out your code. It worked great!!!! However, I couldn't seem to get the CC to work. Here is the modification that I used without the mapi step.

Thanks again...

Public Function SEND_IT()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem

Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = "lcjohnson@kvc.org"
.CC = "prodevmg@kvc.org"
.Subject = "West Virginia Labels (MONTHLY)"
.Body = "***** This is a system generated email. *****" _
& vbNewLine & vbNewLine _
& "ATTACHED IS THE MOST RECENT WEST VIRGINIA FOSTER HOME LABEL LIST."
.Attachments.Add "I:\DEPTDATA\WVLBS\LBL_WV.doc", olByValue, 1, "FOSTER HOME LABELS"
.Send
End With

End Function lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[grannyM]

I've not been able to get the .cc to work in the with MailOutlook block either and I'm sure it's because I'm still learning. However, you can set a cc by adding the following code below the Set MailOutlook line, (without the .cc or anything else extra in the with block)

Set ccRecipient = objNewMail.Recipients.Add("your cc EmailAddress")
ccRecipient.Type = olCC
[\code]

Hope this works for you.  I haven't been able to get an answer to my question on how to include the sender's signature block, so if you run accross that, please let me know!  Thanks!

 

[psimon1]

Is it possible to extend this functionality?

I have a list of departments, department heads, and their email addresses in a table in Access. I also have a query that pulls employee salary and payroll information in another query (and report). If this report has to go to 180 individual people, I can hard code in the departments and the email addresses. However, creating the same report/macro 180 times seems inefficient.

Can I have access automatically look at the department on the report, reference the person's email address on the aforementioned table, and then blast out 180 reports to the different emails?

I would be very interested in what people have done here.

Thanks.

ps

 

[LonnieJohnson]

What is your email address? I have a sample mdb where I did something simular to that.

lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[psimon1]

I tried to email you Lonnie but it didn't work. Your autoresponder indicated that I would never see the message.

psimon@simonhris.com