By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 739 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

how do i export a report through vba? ...and then email it?

n8kindt
100+
P: 221
i'm almost done with this project. one step still remains. here is the background:

we have a statement that needs to be emailed to a certain list of customers every month. however, that report needs to be filtered to display their data only. i have all the coding done for sending the email but i don't know how to send the report as an attachment. here is what i have so far:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Function DAORecordsetLoop()
  4.     Dim rs As dao.Recordset
  5.     Dim strSql As String
  6.     Dim qdf As QueryDef
  7.     Dim db As dao.Database
  8.     Dim DateCalc
  9.     'determine list of customers that will receive the report
  10.     DateCalc = [Forms]![StartEnd]![Year] * 12 + [Forms]![StartEnd]![Month]
  11.     Set db = CurrentDb
  12.     Set qdf = db.QueryDefs("NewQueryDef12")
  13.     qdf.Parameters("[EnterParamHere]") = DateCalc
  14.     Set rs = qdf.OpenRecordset()
  15.  
  16.     Do While Not rs.EOF
  17.         Debug.Print rs!ID
  18.         Debug.Print rs!Email
  19.  
  20.             'NEED TO PLACE CODE HERE TO OPEN THE REPORT AND CHANGE IT TO A .PDF OR HTML FILE_
  21.             'AND SEND IT AS AN ATTACHMENT.
  22.  
  23.  
  24.  
  25.                 Dim objSession As Object
  26.                 Dim objMessage As Object
  27.                 Dim objRecipient As Object
  28.                 Dim msg As String
  29.                 Dim objRecipient2 As Object
  30.  
  31.  
  32.  
  33.                 Set objSession = CreateObject("mapi.session")
  34.  
  35.                 'Logon using the session object
  36.                 'Specify a valid profile name if you want to
  37.                 'Avoid the logon dialog box
  38.                 objSession.Logon profileName:="Microsoft Outlook Internet Settings"
  39.  
  40.                 'Add a new message object to the OutBox
  41.                 Set objMessage = objSession.Outbox.Messages.Add
  42.  
  43.                 'Set the properties of the message object
  44.                 objMessage.Subject = "Your Monthly Report"
  45.  
  46.  
  47.                 'Add a recipient object to the objMessage.Recipients collection
  48.                 Set objRecipient = objMessage.Recipients.Add
  49.                 Set objRecipient2 = objMessage.Recipients.Add
  50.  
  51.                 'Set the properties of the recipient object
  52.                 objRecipient.Name = rs!Email
  53.                 objRecipient2.Name = ""
  54.  
  55.                 'Type can be ActMsgTo, mapiTo, or CdoTo for different CDO versions;
  56.                 'they all have a constant value of 1.
  57.                 objRecipient.Type = 1
  58.                 objRecipient.Resolve
  59.                 objRecipient2.Type = 3
  60.                 objRecipient2.Resolve
  61.                 'Send the message
  62.                 objMessage.Send showDialog:=False
  63.                 'display message to user
  64.                 msg = MsgBox("Message sent! High five!", vbInformation, "Great success!") = vbOK
  65.  
  66.                 'Logoff using the session object
  67.                 objSession.Logoff
  68.                 End
  69.  
  70.  
  71.  
  72.  
  73.  
  74.         rs.MoveNext
  75.  
  76.  
  77.  
  78.  
  79.  
  80.     Loop
  81.     db.Close
  82.     rs.Close
  83.     Set rs = Nothing
  84.     Set db = Nothing
  85.  
  86. End Function
  87.  
  88.  
  89.  
this code works perfectly, but i have no report to send lol. can anyone help me?
May 17 '08 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
You'll first have to produce the reports, as Access only accepts a filename as an attachment. So all rows need to be processed first for each customer and the report saved.
Something like:
Expand|Select|Wrap|Line Numbers
  1. Dim strFileLocation
  2. Dim rs As DAO.Recordset
  3.  
  4. set rs = currentdb.openrecordset("tblCustomers")
  5. while not rs.eof
  6.    strFileLocation = "C:\ReportCust" & rs!Custnumber & ".snp"
  7.    DoCmd.OpenReport "rptCustomers", acViewPreview, , "CustNumber=" & rs!Custnumber, acHidden
  8.    DoCmd.OutputToDoCmd.OutputTo acOutputReport, "rptCustomers", acFormatSNP, strFileLocation
  9.    rs.movenext
  10. wend
  11.  
Now an identical loop for the email with:
Expand|Select|Wrap|Line Numbers
  1.  .Attachments.Add "C:\ReportCust" & rs!Custnumber & ".snp"
  2.  
will produce the required output.

Nic;o)
May 18 '08 #2

n8kindt
100+
P: 221
You'll first have to produce the reports, as Access only accepts a filename as an attachment. So all rows need to be processed first for each customer and the report saved.
Something like:
Expand|Select|Wrap|Line Numbers
  1. Dim strFileLocation
  2. Dim rs As DAO.Recordset
  3.  
  4. set rs = currentdb.openrecordset("tblCustomers")
  5. while not rs.eof
  6.    strFileLocation = "C:\ReportCust" & rs!Custnumber & ".snp"
  7.    DoCmd.OpenReport "rptCustomers", acViewPreview, , "CustNumber=" & rs!Custnumber, acHidden
  8.    DoCmd.OutputToDoCmd.OutputTo acOutputReport, "rptCustomers", acFormatSNP, strFileLocation
  9.    rs.movenext
  10. wend
  11.  
Now an identical loop for the email with:
Expand|Select|Wrap|Line Numbers
  1.  .Attachments.Add "C:\ReportCust" & rs!Custnumber & ".snp"
  2.  
will produce the required output.

Nic;o)

OK! thanks! i was actually ready to post my updated best try at this all... it looks like the only thing i was really missing was the fact that i needed to run the report FIRST. that's where i was stuck. i will post back with the final code for anyone that will need it for future reference. thanks so much nico!!
May 18 '08 #3

n8kindt
100+
P: 221
here's the final code. as far as i know, it works. i really won't know for sure until i send the final report out at the end of may. if someone would like to proof my code and make note of any potential problems i would sure appreciate it.

wow, am i proud of this one....

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Function DAORecordsetLoop()
  4.     Dim rs As dao.Recordset
  5.     Dim strSql As String
  6.     Dim qdf As QueryDef
  7.     Dim db As dao.Database
  8.     Dim DateCalc
  9.     'run report based on report month
  10.     DateCalc = [Forms]![StartEnd]![Year] * 12 + [Forms]![StartEnd]![Month]
  11.     Set db = CurrentDb
  12.     Set qdf = db.QueryDefs("NewQueryDef12")
  13.     qdf.Parameters("[EnterParamHere]") = DateCalc
  14.     Set rs = qdf.OpenRecordset()
  15.  
  16.     Do While Not rs.EOF
  17.         Debug.Print rs!ID
  18.  
  19.  
  20.             Dim TimeH As String
  21.             Dim DateM As String
  22.             Dim DateY As String
  23.             Dim TimeM As String
  24.  
  25.  
  26.             Dim filename As String
  27.             DateM = DatePart("m", Date)
  28.             DateY = DatePart("y", Date)
  29.             Time1 = Hour(Now())
  30.             time2 = Minute(Now())
  31.             filename = "c:\\" & DateM & DateY & TimeM & TimeH & rs!ID & ".pdf"
  32.  
  33.             DoCmd.OpenReport "rptCommissionStatement", acViewPreview, , "Forms!StartEnd!FCFilter = rs!ID", acHidden
  34.             DoCmd.OutputTo acOutputReport, "rptCommissionStatement", acFormatPDF, filename, 0
  35.  
  36.  
  37.                 Dim objSession As Object
  38.                 Dim objMessage As Object
  39.                 Dim objRecipient As Object
  40.                 Dim objAttachment As Object
  41.                 Dim msg As String
  42.                 Dim objRecipient2 As Object
  43.  
  44.  
  45.  
  46.                 Set objSession = CreateObject("mapi.session")
  47.  
  48.                 'Logon using the session object
  49.                 'Specify a valid profile name if you want to
  50.                 'Avoid the logon dialog box
  51.                 objSession.Logon profileName:="Microsoft Outlook Internet Settings"
  52.  
  53.                 'Add a new message object to the OutBox
  54.                 Set objMessage = objSession.Outbox.Messages.Add
  55.  
  56.                 'Set the properties of the message object
  57.                 objMessage.Subject = "Your Monthly Report"
  58.  
  59.  
  60.                 'Add a recipient object to the objMessage.Recipients collection
  61.                 Set objRecipient = objMessage.Recipients.Add
  62.                 Set objRecipient2 = objMessage.Recipients.Add
  63.                 Set objAttachment = objMessage.Attachments.Add
  64.  
  65.                 'Set the properties of the recipient object
  66.                 objRecipient.Name = "mikerotch@gmail.com"
  67.                 objRecipient2.Name = "mikerotch@gmail.com"
  68.                 'objAttachment.Type = CdoFileLink
  69.                 objAttachment.Source = filename
  70.  
  71.  
  72.                 'Type can be ActMsgTo, mapiTo, or CdoTo for different CDO versions;
  73.                 'they all have a constant value of 1.
  74.                 objRecipient.Type = 1
  75.                 objRecipient.Resolve
  76.                 objRecipient2.Type = 3
  77.                 objRecipient2.Resolve
  78.                 'Send the message
  79.                 objMessage.Send showDialog:=False
  80.                 'display message to user
  81.                 msg = MsgBox("Message sent to" & rs!ID & "! High five!", vbInformation, "Great success!") = vbOK
  82.  
  83.                 'Logoff using the session object
  84.                 objSession.Logoff
  85.                 End
  86.  
  87.  
  88.  
  89.  
  90.  
  91.         rs.MoveNext
  92.  
  93.  
  94.  
  95.  
  96.  
  97.     Loop
  98.     db.Close
  99.     rs.Close
  100.     Set rs = Nothing
  101.     Set db = Nothing
  102.  
  103. End Function
  104.  
  105.  
May 18 '08 #4

nico5038
Expert 2.5K+
P: 3,072
Spotted 2 flaws:
1) "Forms!StartEnd!FCFilter = rs!ID" will have to be something like:
"FCFilter =" & rs!ID
Assuming FCFilter is the name in the report's record source.
2) The Attachment.Add will need a filename.

I would test the code using your own email address in a test table.

Nic;o)
May 18 '08 #5

n8kindt
100+
P: 221
Spotted 2 flaws:
1) "Forms!StartEnd!FCFilter = rs!ID" will have to be something like:
"FCFilter =" & rs!ID
Assuming FCFilter is the name in the report's record source.
2) The Attachment.Add will need a filename.

I would test the code using your own email address in a test table.

Nic;o)
good idea. i did test it that way. i amended the code for the 2 flaws you spotted and i found 2 more.

1) i removed the "End" from
Expand|Select|Wrap|Line Numbers
  1.                 'Logoff using the session object
  2.  
  3.                 objSession.Logoff
  4.  
  5.                --->> End
that was causing the code to terminate too early

2) i added
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acReport, "rptCommissionStatement"
right after the output code. without it, it sent the same report over and over. closing it causes it to rerun the report

thanks, nico for all your help!

cheers,
nate
May 19 '08 #6

nico5038
Expert 2.5K+
P: 3,072
You're learning fast Nate :-)

Success with your application !

Nic;o)
May 19 '08 #7

Post your reply

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