Add Attachments to Email-Still Struggling

[froggiebeckie]

Hi, Y'all,
I've tried searching the site for help with this, but I'm still struggling with this email problem. Please take a look and see if you can spot what's wrong.

With code provided on here, the module will now run a query and generate an email based on the query results.
The TO:, SUBJECT:, and MESSAGE are all correct.

This is the code I'm using:

Option Explicit

Public Sub SendFromQuery(Optional attachmentpath)
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("PMs Due Query", dbOpenSnapshot)

With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(7)) = False Then
                sToName = .Fields(7)
                sSubject = "PMs Due : " & .Fields(1)
                sMessageBody = .Fields(0) & "  " & .Fields(1) & "   Due Date  " & .Fields(5)
               
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing

End Sub

The problem is, I need to attach a form to this email. Each record returned in the query includes the path to the specific form needed.
I've tried adding:
Dim sAttach As String and
sAttach = .Fields(8) but
When I try to add sAttach, to the do command statement, it glitches out.

Any ideas on how to get this to attach the file specified in Field 8 of the query?

Once again,
I thank you for your patience and willingness to help.

BeckieO

 

[jwhite]

Have a look at these examples which will give you many more options:
Sending Email and attachments from Access using Automation (MS Outlook)
Sending Email and Multiple Attachments from Access using Automation (MS Outlook)

 

[GaryPanic]

Heres my version - the report is created in a directory then attached


Private Sub Command13_Click()

Dim EmailApp, NameSpace, EmailSend As Object

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

EmailSend.To = [Forms]![claimsreporter]![text8]
EmailSend.Subject = "Claims Report for" & " " & [Forms]![claimsreporter]![Text3]
'EmailSend.Message = "h:\email.txt" (THIS FAILED)
EmailSend.Attachments.Add "C:\temp\cancellation.xls"
EmailSend.Display

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

'Kill "C:\temp\test2.xls"

End Sub

 

[ian_w]

As said it is .Attachments.Add = "Path to file"

 

[froggiebeckie]

Thanks, all!
I'll work with these suggestions and let you know how it turns out.

Wish me luck!!

BeckieO

 

[froggiebeckie]

Got It!!

I hope this helps someone else a little bit.

OK, finally got something that works. I ended up using bits and pieces from all over, so some of this may be redundant, but the important part is, it works.

I made an AutoExec macro to run the code, so when I open the db it:
Runs a query to determine what equip is due for pm
Generates an email with the required addresses, subjects, message body and attachments.
Sends it out.

I use ClickYes software so I don't get the "Someone's trying to send an Email" warnings.

Here's the code:

Option Explicit

Public Function SendReminder()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim Attach As String


Set fso = New FileSystemObject

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)


' This addresses it
MyMail.To = MailList("email")
' This adds addresses to Copy
MyMail.CC = MailList("Copy To")
'This gives it a subject
MyMail.Subject = MailList("subject") & " Due"
'This gives it the body
MyMail.Body = MailList("equipment") & " - " & MailList("Freq") & " Day " & MailList("subject") & " - Due " & MailList("DueDate")
'If you want to send an attachment
'uncomment the following line
Attach = MailList("Attachment File Path")
MyMail.Attachments.Add Attach

'This sends it!
MyMail.Send
'To Display instead of Send, Uncomment the next line
'And comment the "MyMail.Send" line above this.
'MyMail.Display
'And on to the next one...
MailList.MoveNext
Loop



'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function

As always, thanks so much for all your help.
I'd never have gotten it without y'all.

BeckieO