Send email with >1 attchmt with filtering

[Jessica]

Hi, I don't know if this can be done in a macro or if I need script, but I
want to send a table and 5 reports (all filtered to a city) to an email
address. Any tips? The table will be exported to Excel and the Reports to
Word.

 

[Steve Schapel]

Jessica,

I think about the only way this can be done with a macro is to use an
OutputTo action for each of the items for sending except one, which will
save these files to disk, and then for the 6th item, use a SendObject
action in your macro, with the Edit Message argument set to Yes. Then,
when the email opens in your email client, you can manually attach the
other 5 files before sending.

 

[Chris Reveille]

This is the complete Email code. I obtained this from one
of the MVP's(Forgot wich one).
I have seen quite a bit of code for sending email. I have
tried many of them. If you are just sending an Access
report you can use the SendObject command. If attachments
are files of any type try the code below. The one below( I
have it stored as a function in a new module) has been the
most reliable for me with MS Outlook and Access 2000 and
can send up to 5 attachments or none. Also I am able to
pull from an open form email address of the recipient, CC,
BCC, and the subject and text and file name of the
attachment(s).
To reference data on an open form use
=[forms]![formname].[emailaddress]
=[forms]![formname].[subject]
=[forms]![formname].[attachment1]
ect.


Good Luck

Chris

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String,strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for body here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
strAttachment2 = "attachment3"
strAttachment3 = "attachment4"
strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
If Len(strAttachment2) <> 0 Then
olMail.Attachments.Add (strAttachment2)
If Len(strAttachment3) <> 0 Then
olMail.Attachments.Add (strAttachment3)
If Len(strAttachment4) <> 0 Then
olMail.Attachments.Add (strAttachment4)
End If
End if
End if
End if
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

chris