469,303 Members | 2,042 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,303 developers. It's quick & easy.

How to email an Excel file that has been generated by MS Access query in VBA?

26
Currently, the following code creates a spreadsheet from an Access query and sends an email containing a link to the location of the saved file. Is there a way I can attach the spreadsheet to the email being sent, rather than just give the email recipient a link? Thank you.
Expand|Select|Wrap|Line Numbers
  1.     Dim OutApp As Object
  2.     Dim OutMail As Object   
  3.  
  4.     Mo = Format(Now(), "mm")
  5.     Dy = Format(Now(), "dd")
  6.     Yr = Format(Now(), "yyyy")
  7.     accessPath = CurrentProject.Path
  8.  
  9.     EpicFilePath = accessPath & "\CheckTerminations\Epic\Epic_Terminations" & Yr & Mo & Dy & ".xlsx"
  10.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryTerminatedEpic", EpicFilePath, True
  11.     DoCmd.SetWarnings True
  12.  
  13.     EmailTo = DLookup("RESCIND_EMAIL", "SYSTEM_MASTER", "SYSTEM_NAME = 'EPIC'")
  14.     Emailbcc = "XYZ@ABC.net"
  15.  
  16.     FileText = EpicFilePath
  17.  
  18.     IntroText = "Epic terminations have been exported to..."
  19.  
  20.     EndText = "Thank you."
  21.  
  22.     DoCmd.SetWarnings False
  23.  
  24.     Set OutApp = CreateObject("Outlook.Application")
  25.     Set OutMail = OutApp.CreateItem(olMailItem)
  26.     With OutMail
  27.         .To = EmailTo
  28.         .bcc = Emailbcc
  29.         .Subject = "Epic Terminations"
  30.         .Body = IntroText & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
  31.                 & FileText & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
  32.                 & EndText
  33.         .Send
  34.     End With
  35.     DoCmd.SetWarnings True
Feb 17 '12 #1
14 8244
ADezii
8,800 Expert 8TB
Refer to Code Line# 12
Expand|Select|Wrap|Line Numbers
  1. '******************* Code Intentionally Omitted ********************
  2. Set OutApp = CreateObject("Outlook.Application")
  3. Set OutMail = OutApp.CreateItem(olMailItem)
  4.  
  5. With OutMail
  6.   .To = EmailTo
  7.   .BCC = Emailbcc
  8.   .Subject = "Epic Terminations"
  9.   .Body = IntroText & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
  10.           & FileText & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
  11.           & EndText
  12.   .Attachments.Add EpicFilePath
  13.     .Send
  14. End With
  15. '******************* Code Intentionally Omitted ********************
  16.  
Feb 17 '12 #2
3c4j5d
26
Fantastic! Thank you very much!!
Feb 17 '12 #3
ADezii
8,800 Expert 8TB
You are quite welcome.
Feb 17 '12 #4
3c4j5d
26
ADezii, I have taken this code and was trying to get it to run through Windows Scheduler by creating its own database, with one form that has this code on the OnOpen event. When I test it, it opens the db, and the code starts running, but then stops on the DoCmd.TransferSpreadsheet command with Run-Time error 3434 'Cannot expand named range' . Can you help me with this also, or should I submit a new question?
Thanks.
Feb 17 '12 #5
ADezii
8,800 Expert 8TB
Does DoCmd.TransferSpreadsheet operate normally in and of itself?
Feb 17 '12 #6
3c4j5d
26
Hi ADezii,
Yes, the DoCmd.TransferSpreadsheet works fine; just not when I scheduled it to run.
Feb 20 '12 #7
NeoPa
32,173 Expert Mod 16PB
Invoking a Database From the Command Line may help with that, but it sounds like you already have that part working.

NB. Pay close attention to the explanation of how the scheduler works with accounts though. If you're trying to access any network resources then it'll fail unless you manage that within the scheduler properly.

If you share your details here we may be able to help. This is a Windows/Networking issue really, but no worries as I can cover it from here and I'm not sure they'd understand about the Access side there very well typically.
Feb 20 '12 #8
3c4j5d
26
In Windows Scheduler, Run: is set to \\Sunmapp02p\tibcobpm\MSAudit\Dev\DevTermReports.a ccdbI will attach a document showing how the user account is set up and how the job is scheduled. Please let me know if there is more information you would need. Thank you very much!
Attached Files
File Type: docx winsched_userinfo.docx (197.4 KB, 388 views)
Feb 20 '12 #9
NeoPa
32,173 Expert Mod 16PB
Is the SMRCY\MAAR_Support account configured for use on that PC? Do you use that account normally when working on that PC?

Otherwise the information was more than adequate. Well done :-)

I would suggest that it's never a good idea to allow an Access task to be terminated unnaturally though. In page #3, "Stop the task if battery mode begins", could have that result, but that would be far more damaging than letting the battery go down a little. Not starting the task should be more than adequate protection for this, I'd suggest.

Otherwise you already have a very well set up situation there. If you got all that from my article then I'd like to congratulate you on your attention to detail in following instructions.
Feb 20 '12 #10
3c4j5d
26
Thank you, NeoPa for the information. I did make the change in the scheduler as you suggested. I opened the database manually (as MAAR_Support), and I received the following Outlook messages....one for each of the reports I was emailing. When I clicked on "Allow" each time, the reports were emailed successfully. I then tried scheduling the procedure again, and this time it went through just fine. I guess the Outlook error with a required reply was causing the 3434 error? It does seem to work fine now, through the scheduler. Thank you very much!!
Feb 20 '12 #11
3c4j5d
26
I forgot to attach the Outlook error...will do that now....thanks.
Attached Files
File Type: docx OutlookError.docx (22.2 KB, 388 views)
Feb 20 '12 #12
NeoPa
32,173 Expert Mod 16PB
Pleased to hear that went well :-)

NB. You can attach pictures as JPEGs, and even show them in the post with [IMGNOTHUMB] or [IMG] tags. See BB Code for more.
Feb 20 '12 #13
I use Total Access Emailer from FMS. It lets you email data from Access directly. You can create a query and tell it to email it in Excel format (you can also attach reports as PDF files).

What's cool is you can have the query filtered for each contact in your email list so each person gets their own data. That way a whole bunch of personalized emails can be broadcast at once. It runs as an add-in that doesn't require programming. There's a VBA function to call if you want to automate it.

More info and a free trial from their site: http://www.fmsinc.com/MicrosoftAccess/Email.asp
Feb 27 '12 #14
3c4j5d
26
Thank you, DataAnalyzer. Very interesting....will check it out.
Thanks!
Feb 27 '12 #15

Post your reply

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

Similar topics

2 posts views Thread by laurasesma18 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.