Having trouble attaching report and saving in Drafts folder

[anoble1]

Guys,
I have been trying different things and not able to get my attachment to attach and save in my outlook. A blank email comes up with the users name in the To section but no attachment. How do I add and save my attachment report?

Here is what I have. I need some help

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String
 
 
    'mypath = "C:\Users\anoble\Documents\Test\"
 
    Set db = CurrentDb()
 
    Set rs = db.OpenRecordset("SELECT [email] FROM [tbl2018Extra]", dbOpenSnapshot)
 
    Do While Not rs.EOF
 
        temp = rs("EMAIL")
        MyFileName = rs("EMAIL") & ".PDF"
 
        Set myOutlook = New Outlook.Application
        Set mymail = myOutlook.CreateItem(olMailItem)
 
        DoCmd.OpenReport "rptPay", acViewReport, , "[email]='" & temp & "'"
        'DoCmd.SendObject "rptPay", acSendObject, , temp
        'DoCmd.OutputTo acOutputReport, "rptPay", acFormatPDF, "[e-mail]
        DoCmd.OutputTo acOutputReport, "rptPay", "PDFFormat(*.pdf)", "rptPay", False
        'mymail.To = temp
        mymail.To = temp
        'DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
        'DoCmd.Close acReport, "rptPay"
        mymail.Save
        DoEvents
 
        rs.MoveNext
    Loop
 
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub

[twinnyfo]

I think the solution is

Mymail.Attacchments.Add [path and filename]

[anoble1]

So, what I ended up doing was saving the report to a folder calling it "Report" then after running before the Loop deleting that file in the folder and going to the next record. Seemed to work

[NeoPa]

Just for curiosity, the suggested solution that appears more straightforward was unacceptable because ...?

[anoble1]

See post below - page refreshed automatically

[anoble1]

Yeah, that's what I ended up doing. I was hoping I could add the attachment not having to export it to a folder but was not able. I used the:

mymail.Attachments.Add

Here is what I ended up doing.

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String
 
 
    mypath = "C:\Users\anoble\Documents\Test\"
 
    Set db = CurrentDb()
 
    Set rs = db.OpenRecordset("SELECT tbl2018Extra.EMAIL, tbl2018Extra.[Grand Total], tbl2018Extra.SALESMAN FROM tbl2018Extra WHERE (((tbl2018Extra.EMAIL) Is Not Null) AND ((tbl2018Extra.[Grand Total])>0));", dbOpenSnapshot)
 
    Do While Not rs.EOF
 
        temp = rs("EMAIL")
        MyFileName = rs("EMAIL") & ".PDF"
        SirName = rs("Salesman")
 
        Set myOutlook = New Outlook.Application
        Set mymail = myOutlook.CreateItem(olMailItem)
 
        DoCmd.OpenReport "rptPay", acViewReport, , "[email]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "rptPay", acFormatPDF, mypath & "Bonus" & ".pdf"
 
        mymail.To = temp
        End If
 
        mymail.Attachments.Add "C:\Users\anoble\Documents\Test\Bonus.pdf"
        mymail.Subject = "Bonus Report" & " - " & Format(DateAdd("m", -1, Date), "mmmm yyyy") & " - " & SirName
        mymail.Body = "Attached are your Calculated Bonuses YTD" & " - " & Format(DateAdd("m", -1, Date), "mmmm yyyy") '& strBuffer
        mymail.Save
        Kill "C:\Users\anoble\Documents\Test\*.*"
        DoEvents
 
        rs.MoveNext
    Loop
 
 
        DoCmd.Close acReport, "rptPay"
        MsgBox ("Done")
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub
 

[NeoPa]

Ah. That's much clearer then :-) I'll just set the post as Best Answer then. Cheers for the feedback.

Doh!! No I won't as it's already been done.

Note to self - book that eye test (then allow the doctor to run that alzheimers test I've been avoiding).