UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Automatically Send Email With Report Attached, Access 2007    
 
   
KimFarm
post Jun 17 2013, 03:36 PM
Post#1



Posts: 50
Joined: 19-September 12



I've created a macro that runs automatically when I open Access, attaches a report to an email, and then stands ready for me to hit the "Send" button to send it out. That part works perfectly, however I need it to send out the report without any intervention, preferably with me not having to open Access first. I am hesitant to use a third party solution, as if I am no longer with the company, or the solution gets installed locally on my computer and I get a replacement computer, that solution may not work, but I am open to suggestions. How can I automate the send of this email?
Thank you!
Go to the top of the page
 
strBean
post Jun 17 2013, 04:03 PM
Post#2


UtterAccess VIP
Posts: 4,892
Joined: 25-September 02
From: Washington State


Although I do not use Access macros (I use VBA) I am confident your task is do-able.
) If you are using the macro action "EMailDatabaseObject" set the "Edit Message" argument to No, and make sure the To address is written into the To argument. That should cause the email to be sent without displaying.
2) Name the macro "AutoExec" and it will run when the DB is opened.
3) Create a Scheduled Task in Windows to run the project at the appropriate time.
Best of luck on your project!
Go to the top of the page
 
KimFarm
post Jun 18 2013, 09:46 AM
Post#3



Posts: 50
Joined: 19-September 12



Ah, I had forgotten to set the "Edit" to "No". That addressed the issue of the email sitting there, however I still get an Outlook message asking permission (with Allow and Deny buttons) to send the email. What would be the best solution to work through that step?
Thank you!
Go to the top of the page
 
strBean
post Jun 18 2013, 11:26 AM
Post#4


UtterAccess VIP
Posts: 4,892
Joined: 25-September 02
From: Washington State


There are a couple ways to handle that. I hadn't really thought about that little Outlook security thing when I suggested how to fully automate this process. When you use the EMailDatabaseObject macro action, Outlook will always throw up that warning. Even if you use the SendObject method in VBA, you will have that trouble.
Years ago, I used a little free application called Express ClickYes to get around the Outlook security thing. I don't know if it works with Access 2007 or 2010 but you might try a web search and see. If you launch that little program before your Access application tries to send an email, ClickYes will automatically click the Allow button on the warning message each time it comes up. You could launch the ClickYes program in your macro, before you invoke the EMailDatabaseObject action. This is a workaround, and many Access developers may frown on it.
The reason it didn't occur to me that the Outlook warning would thwart your automation is that not only do I not use macros, I also don't use the SendObject method - I always write out the code to fully automate Outlook when I need an Access application to send an email, and when I do that, I don't get the Outlook security warnings.
So, 2 possible solutions:
1) use the ClickYes program I mentioned, if it's still available, or
2) learn how to do this with VBA
Incidentally, if it were me in my work situation, I would not be able to install that ClickYes program (no admin rights) so I'm stuck with VBA. Don't know about your situation...
Go to the top of the page
 
KimFarm
post Jun 18 2013, 01:21 PM
Post#5



Posts: 50
Joined: 19-September 12



Thank you for your reply! Would you be willing to share your VBA code, as I am in the same boat as you, no admin rights. Thank you again for taking the time to assist me!
Go to the top of the page
 
strBean
post Jun 18 2013, 02:17 PM
Post#6


UtterAccess VIP
Posts: 4,892
Joined: 25-September 02
From: Washington State


With the following, you'll need to set a reference to the Outlook object library:
CODE
Public Sub SendReport()
On Error GoTo Whoops
  
  'declare variables
  Dim ol As Outlook.Application, msg As MailItem, atts As Attachments, strFilePath As String
  Set ol = CreateObject("Outlook.Application")
  
  'prepare the attachment
  strFilePath = "\\servername\foldername\reportname.pdf" ' edit this - it's where you will store the report
                         ' and then retrieve it to attach it to the email.
  DoCmd.OutputTo acOutputReport, "report name (edit this)", acFormatPDF, strFilePath, False
  DoEvents
  
  'prepare the message
  Set msg = ol.CreateItem(olMailItem)
  Set atts = msg.Attachments
  With msg
    .Subject = "subject line" 'edit this
    .Body = "message" 'edit this
    .To = "recipient(s)" 'edit this
    .CC = "more recipients" 'edit this
    atts.Add strFilePath
    .Send
  End With
  
  'clean up
Offramp:
  If Not msg Is Nothing Then Set msg = Nothing
  If Not ol Is Nothing Then Set ol = Nothing
  Exit Sub
  
  'error handler
Whoops:
  Select Case Err
    Case Else
      MsgBox "Error #" & Err & ": " & Err.Description
      Resume Offramp
  End Select
End Sub

If this application might run in more than one version of Access, use late-binding for the object variables, so you don't need the Outlook library reference, which can easily be broken:
CODE
Public Sub SendReportLateBinding()
On Error GoTo Whoops
  
  'declare variables -  note declaration As Object instead of As Outlook.Application, etc.
  Dim ol As Object, msg As Object, atts As Object, strFilePath As String  Set ol = CreateObject("Outlook.Application")
  
  'prepare the attachment
  strFilePath = "\\servername\foldername\reportname.pdf" ' edit this
  DoCmd.OutputTo acOutputReport, "report name (edit this)", acFormatPDF, strFilePath, False
  DoEvents
  
  'prepare the message
  Set msg = ol.CreateItem(0)  ' if you compare this line with its counterpart in the early-binding example,
                                            ' you'll see that the value of the constant olMailItem is zero
  Set atts = msg.Attachments
  With msg
    .Subject = "subject line" 'edit this
    .Body = "message" 'edit this
    .To = "recipient(s)" 'edit this
    .CC = "more recipients" 'edit this
    atts.Add strFilePath
    .Send
  End With
  
  'clean up
Offramp:
  If Not msg Is Nothing Then Set msg = Nothing
  If Not ol Is Nothing Then Set ol = Nothing
  Exit Sub
  
  'error handler
Whoops:
  Select Case Err
    Case Else
      MsgBox "Error #" & Err & ": " & Err.Description
      Resume Offramp
  End Select
End Sub

Be sure to edit the code where I've indicated...
have fun and write back!
Go to the top of the page
 
dlafko
post May 6 2019, 01:35 PM
Post#7



Posts: 217
Joined: 7-May 14



Hello,
Just reading this post and not sure I understand it all but my question, I have a table with email addresses is there a way to get the TO: for the email to automatically grab from that list instead of having to preenter them in the code or enter it after outlook opens?
Go to the top of the page
 
June7
post May 6 2019, 01:44 PM
Post#8



Posts: 1,033
Joined: 25-January 16



Should have started your own thread instead of piggy-backing on an old one.

Automating email is a common topic. Open recordset of email addresses and loop through records and send email to each address. Or build a string of addresses for the CC property and send 1 email.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dlafko
post May 6 2019, 01:46 PM
Post#9



Posts: 217
Joined: 7-May 14



Ok sorry will do just grabbed the code. Not sure what you mean, new to coding but I will make a separate post to explain better..

Thanks
Sorry again.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 05:04 AM