423,323 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,323 IT Pros & Developers. It's quick & easy.

Having trouble attaching report and saving in Drafts folder

anoble1
100+
P: 214
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

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim rs As DAO.Recordset
  3.     Dim MyFileName As String
  4.     Dim mypath As String
  5.     Dim temp As String
  6.  
  7.  
  8.     'mypath = "C:\Users\anoble\Documents\Test\"
  9.  
  10.     Set db = CurrentDb()
  11.  
  12.     Set rs = db.OpenRecordset("SELECT [email] FROM [tbl2018Extra]", dbOpenSnapshot)
  13.  
  14.     Do While Not rs.EOF
  15.  
  16.         temp = rs("EMAIL")
  17.         MyFileName = rs("EMAIL") & ".PDF"
  18.  
  19.         Set myOutlook = New Outlook.Application
  20.         Set mymail = myOutlook.CreateItem(olMailItem)
  21.  
  22.         DoCmd.OpenReport "rptPay", acViewReport, , "[email]='" & temp & "'"
  23.         'DoCmd.SendObject "rptPay", acSendObject, , temp
  24.         'DoCmd.OutputTo acOutputReport, "rptPay", acFormatPDF, "anoble@tec1943.com
  25.         DoCmd.OutputTo acOutputReport, "rptPay", "PDFFormat(*.pdf)", "rptPay", False
  26.         'mymail.To = temp
  27.         mymail.To = temp
  28.         'DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
  29.         'DoCmd.Close acReport, "rptPay"
  30.         mymail.Save
  31.         DoEvents
  32.  
  33.         rs.MoveNext
  34.     Loop
  35.  
  36.  
  37.     rs.Close
  38.     Set rs = Nothing
  39.     Set db = Nothing
  40.  
  41. End Sub
May 10 '18 #1

✓ answered by twinnyfo

I think the solution is
Expand|Select|Wrap|Line Numbers
  1. Mymail.Attacchments.Add [path and filename]

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,536
I think the solution is
Expand|Select|Wrap|Line Numbers
  1. Mymail.Attacchments.Add [path and filename]
May 11 '18 #2

anoble1
100+
P: 214
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
May 16 '18 #3

NeoPa
Expert Mod 15k+
P: 30,996
Just for curiosity, the suggested solution that appears more straightforward was unacceptable because ...?
May 17 '18 #4

anoble1
100+
P: 214
See post below - page refreshed automatically
May 17 '18 #5

anoble1
100+
P: 214
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:
Expand|Select|Wrap|Line Numbers
  1. mymail.Attachments.Add
Here is what I ended up doing.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim rs As DAO.Recordset
  3.     Dim MyFileName As String
  4.     Dim mypath As String
  5.     Dim temp As String
  6.  
  7.  
  8.     mypath = "C:\Users\anoble\Documents\Test\"
  9.  
  10.     Set db = CurrentDb()
  11.  
  12.     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)
  13.  
  14.     Do While Not rs.EOF
  15.  
  16.         temp = rs("EMAIL")
  17.         MyFileName = rs("EMAIL") & ".PDF"
  18.         SirName = rs("Salesman")
  19.  
  20.         Set myOutlook = New Outlook.Application
  21.         Set mymail = myOutlook.CreateItem(olMailItem)
  22.  
  23.         DoCmd.OpenReport "rptPay", acViewReport, , "[email]='" & temp & "'"
  24.         DoCmd.OutputTo acOutputReport, "rptPay", acFormatPDF, mypath & "Bonus" & ".pdf"
  25.  
  26.         mymail.To = temp
  27.         End If
  28.  
  29.         mymail.Attachments.Add "C:\Users\anoble\Documents\Test\Bonus.pdf"
  30.         mymail.Subject = "Bonus Report" & " - " & Format(DateAdd("m", -1, Date), "mmmm yyyy") & " - " & SirName
  31.         mymail.Body = "Attached are your Calculated Bonuses YTD" & " - " & Format(DateAdd("m", -1, Date), "mmmm yyyy") '& strBuffer
  32.         mymail.Save
  33.         Kill "C:\Users\anoble\Documents\Test\*.*"
  34.         DoEvents
  35.  
  36.         rs.MoveNext
  37.     Loop
  38.  
  39.  
  40.         DoCmd.Close acReport, "rptPay"
  41.         MsgBox ("Done")
  42.     rs.Close
  43.     Set rs = Nothing
  44.     Set db = Nothing
  45.  
  46. End Sub
  47.  
May 17 '18 #6

NeoPa
Expert Mod 15k+
P: 30,996
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).
May 17 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.