Solved: Attach Word File to Email

[Tricia]

I am looking for code to attach word files to emails and send them to different people based on a table in access. Any one have any ideas?

[Anne Troy]

Hi, Tricia.

So....will it always be the same Word file? Or is the name/path of the file in a field somewhere? Where is the email address? I assume a field, and the coders will need to know that field's name too.

If possible, can you make a copy of your database, remove anything not related, change the names to protect the innocent, compact, zip, and ship up here?

[Tricia]

I have attached the database. the files will be the check number .rtf and the email adress associated with that file is listed as well in the table numberandemail.

[___]

I have attached the database. the files will be the check number .rtf and the email adress associated with that file is listed as well in the table numberandemail.

Will all the .rtf files be in the same folder? Are the files really named 1.rtf, 2.rtf etc,etc? Anyway, I've created a new form in your db called frmMail. The code to send the email is as below...
[vba]
Private Sub CmdSendMail_Click()
On Error GoTo Err_CmdSendMail_Click
Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = Me.Mail_Address_S
EmailSend.Subject = "Latest Document"
EmailSend.Body = "Please find attached the latest Word document" & vbCrLf & vbCrLf & "Best Regards" & vbCrLf & "Tricia"
EmailSend.Attachments.Add "C:\" & Me.CheckNumber & ".rtf"
EmailSend.Display

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

Exit_CmdSendMail_Click:
Exit Sub

Err_CmdSendMail_Click:
If Err Then
MsgBox "The file was not found"
Resume Exit_CmdSendMail_Click
Else
End If

End Sub
[/vba]
you will need to change this line...
[vba]
EmailSend.Attachments.Add "C:\" & Me.CheckNumber & ".rtf"
[/vba]
to the folder where you store the files, ie.
[vba]
EmailSend.Attachments.Add "C:\MyDocuments\LatestDocs\" & Me.CheckNumber & ".rtf"
[/vba]
If you need anymore help let me know.

[Tricia]

That works thanks but, I was looking for a little more automated version. I want it to loop through the whole table with one push of the button and send the e-mails out with out pushing send each time. Any suggestions?

[mark007]

To send it straight away rather than display it just change:

EmailSend.Display

to

EmailSend.Send

[___]

See if this works. I've not had time to test it because I've got to run to see the football!

Let me know.
If it doesn't I'll look closer at it tomorrow.

[Anne Troy]

Okay, guys. That's 2 down to go watch football (which is really soccer), but let us know how the game goes...

[___]

let us know how the game goes...

Eng 3 - Sui 0 *hic*

[___]

Ok, I've tested it now and this version works. I created a listbox called LstEMail with the Row Source query as below...

SELECT numberandemail.CheckNumber, numberandemail.[Mail Address S]
FROM numberandemail;

then looped through each record in the list...

[vba]
Private Sub CmdSendToList_Click()
On Error GoTo Err_CmdSendToList_Click

Dim intCurrRow As Integer
Dim EmailApp, NameSpace, EmailSend As Object

For intCurrRow = 0 To LstEMail.ListCount - 1

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = Me.LstEMail.Column(1, intCurrRow)
EmailSend.Subject = "Latest Document"
EmailSend.Body = "Please find attached the latest Word document" & vbCrLf & vbCrLf & "Best Regards" & vbCrLf & "Tricia"
EmailSend.Attachments.Add "C:\" & Me.LstEMail.Column(0, intCurrRow) & ".rtf"
EmailSend.Display

Next intCurrRow

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

Exit_CmdSendToList_Click:
Exit Sub

Err_CmdSendToList_Click:
Resume Next

End Sub
[/vba]

and as Mark007 said in his earlier post, to send it straight away rather than display it just change:
[vba]
EmailSend.Display
[/vba]
to
[vba]
EmailSend.Send
[/vba]
Hope it's what you wanted.

[___]

Did it work as you wanted?

[Tricia]

It Works!!! Case Closed. Thank you!