sending a email with more than 1 attachment 1

[jeffmoore]

I need to send an email with 2 attachments...
Here is my code segment.
On Error GoTo Err_MailReport_btn_Click

Dim EmailSubject As String
Dim EmailBody As String
Dim DB As Database: Set DB = DBEngine(0)(0)
Dim RS As Recordset: Set RS = DB.OpenRecordset("TPPM Email List&quot
Dim Victim As Field: Set Victim = RS!EmailAddress
Dim stDocName As String: stDocName = "Month End Reports"
stEmailSubject = "Month End Reports"
email = Victim
RS.MoveNext

While RS.EOF = False
email = email + ";" + Victim
RS.MoveNext

Wend

DoCmd.SendObject acReport, stDocName, "SnapshotFormat(*.snp)", _
email, "", "", stEmailSubject, _
stEmailBody, False

Set Victim = Nothing
RS.Close: Set RS = Nothing
Set DB = Nothing

Exit_MailReport_btn_Click:
Exit Sub

Err_MailReport_btn_Click:
MsgBox Err.Description
Resume Exit_MailReport_btn_Click

End Sub

 

[danvlas]

SendObject sends only one attachment.
For more, you have to use Automation with Outlook, CDONTS, EasyMail or any other mail program that exposes its objects and collections.

With Outlook:

First output the reports you need. Instead of DoCmd.SendObject, use DoCmd.OutputTo

Then, grab the files you saved and build a list/recordset/array of attachments, then:

Set objOutlook = CreateObject("Outlook.Application&quot
Set itmMail = objOutlook.CreateItem(0)
itmMail.To = "rec1@company.com; rec2company.com"
itmMail.CC = "rec3@company.com; rec4company.com"
itmMail.BCC = "rec5@company.com; rec6company.com"
itmMail.Subject = "This is the subject line"
itm.Body = "This is the message body"
'You can use a loop to attach the files from the list/recordset/array of attachments
itm.Attachments.Add "C:\Path\File1.txt"
itm.Attachments.Add "C:\Path\File2.txt"

itm.Send
Set itm = Nothing
Set objOutlook = Nothing


A note:
email = email + ";" + Victim
may result in a Null value if any Victim field value is null. That's because you use '+' as concatenating operator.

I'd use:
email = email & (";" + Victim)

This will preserve the 'email' string and will only add the non-nulls from the Victim field. The semicolon is also ignored for Null Victims...

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jeffmoore]

First thanks for the code segments and suggestions.
There are 2 problems;
1) Someplace after both snapshot files are outputed I get an error "Object required"
2) Access or VBA wont stop running at any breakpoint i set.
hence I'm not sure where the proir error is occuring.

Below is all the code:
TIA
jeff


Dim EmailSubject As String
Dim EmailBody As String
Dim DB As Database
Set DB = DBEngine(0)(0)
Dim RS As Recordset
Set RS = DB.OpenRecordset("TPPM Email List&quot
Dim Victim As Field
Set Victim = RS!EmailAddress
Dim stDocName As String

DoCmd.OutputTo acOutputReport, "TPPMALine", acFormatSNP, "c:\windows\temp\tppmaline.snp", False
DoEvents
DoCmd.OutputTo acOutputReport, "TPPMBLine", acFormatSNP, "c:\windows\temp\tppmbline.snp", False
DoEvents
email = Victim
RS.MoveNext
While RS.EOF = False
email = email & (";" + Victim)
RS.MoveNext
Wend

Set objOutlook = CreateObject("Outlook.Application&quot
Set itmMail = objOutlook.CreateItem(0)
itmMail.To = email
itmMail.CC = ""
itmMail.BCC = ""
itmMail.Subject = "Month End Reports"
itm.Body = "This is the message body"
'You can use a loop to attach the files from the list/recordset/array of attachments
itm.Attachments.Add "c:\windows\temp\tppmaline.snp"
itm.Attachments.Add "c:\windows\temp\tppmbline.snp"

itm.Send
Set itm = Nothing
Set objOutlook = Nothing
Set Victim = Nothing
RS.Close: Set RS = Nothing
Set DB = Nothing

Exit_MailReport_btn_Click:
Exit Sub

Err_MailReport_btn_Click:
MsgBox Err.Description
Resume Exit_MailReport_btn_Click

End Sub

 

[danvlas]

Sorry...my mistake.
Replace itm with itmMail

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dschomburg]

danvlas,
I also am in need of this. I already have the files. When I try to execute this as a function I receive a "variable not defined" error for 'objOutlook' and 'inmMail'
How to I dim these befor Setting the value?
Or am I missing something altogether.


Set objOutlook = CreateObject("Outlook.Application&quot
Set itmMail = objOutlook.CreateItem(0)

itmMail.To = "her@here.com; him@there.com"
itmMail.CC = "me@here.com"
itmMail.BCC = "them@someplace.com"
itmMail.Subject = " Subject "
itmMail.Body = "BODY"

itmMail.Attachments.Add "C:\Pivot_Exports\Canada Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\HI Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU109 Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU110 Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU110GM Adds.xls"

itmMail.Send
Set itmMail = Nothing
Set objOutlook = Nothing

End Function


Thanks,
Dave

 

[jeffmoore]

check and see if you have "option explicit" set in your declarations.
Thanks danvals that did the trick.
jeff

 

[dschomburg]

I think it was the bit in CreateItem that was stopping me. I made some changes and found that this works.
Thanks for the guide!

Function MailTest()
Dim objOutlook As Outlook.Application
Dim itmMail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application&quot
Set itmMail = objOutlook.CreateItem(olMailItem)


itmMail.To = "Her@here.com; him@there.com"
itmMail.CC = "me@here.com"
itmMail.BCC = "them@someplace.com"
itmMail.Subject = " SUBJECT "
itmMail.Body = "BODY"

itmMail.Attachments.Add "C:\Pivot_Exports\Canada Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\HI Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU109 Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU110 Adds.xls"
itmMail.Attachments.Add "C:\Pivot_Exports\BU110GM Adds.xls"

itmMail.Send
Set itmMail = Nothing
Set objOutlook = Nothing

End Function

Works like a charm
Thanks again,
Dave

 

[danvlas]

Dim objOutlook As Oject
Dim itmMail As Object




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html