Macro to Email Report through Outlook

[kvn5x4]

Everyone,



I am having some trouble with this project I have been working on. I researched a ton for it, but have yet to find a solution.

Background: XX number of units are inputted into a table on a daily basis. This table holds all the inputs for the entire year. A query has been generated where it filters the # of units based on the date they were inputted. In my case, they are filtered for only showing the units entered the day before. A report is then generated to be emailed to the supervisors to determine their output (how many units were completed for that day). I created a macro that emails this report to these supervisors.

The scope: In the macro, I utilized "Emaildatabase Object" and I can get it to work with my Outlook 2010. However, it is requiring me to hit the "send" button every day this macro runs. I am wanting it to send automatically with no user input.

Additionally, I am attempting to use a batch file to run this macro and schedule it to run with MS windows scheduler. So in the end, I am wanting to automate this process where it will email a daily report automatically. However, this batch file will open MS access but will not run the macro (atleast I think)

I have posted my code below for the macro and the batch file. Your help is very appreciated!

Macro:


Batch file:

"C:\Program Files\Microsoft Office\Office14MSACCESS.EXE" "C:\Users\Kyle\Desktop\update.accdb" /x mcrRunMacro1

[alansidman]

While these videos are in earlier versions of Access, I think you should be able to do the same in your version.

http://www.datapigtechnologies.com/f...sendemail.html

[kvn5x4]

I think you may have misunderstood me. When I run the macro, the email is generated (as shown in the second picture) and requires me to hit the send button on the email. I do not want to have to do this on a daily basis. I want it to send automatically WITHOUT me hitting the send button

On a further note, I never get that message as shown in the video you provided. The one where a warning is displayed saying an email is being sent, etc.

[Xipooo]

I'd drop doing it in a Macro and do it in VBA instead.

I haven't tested this, but I think it should work..

    Dim OApp As Object
    Dim OMail As Object
    Dim OAttachments As Object
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(olMailItem)
    Set OAttachments = OMail.Attachments
    
    DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, CurrentProject.Path & "ReportName.pdf", False
    
    OMail.Body = "Enter Body Of Email Here"
    OMail.Subject = "Enter Subject Here"
    OMail.To = "EmailAddress@Wherever.com"
    OAttachments.add CurrentProject.Path & "ReportName.pdf", 1
    OMail.send

    OAttachments = nothing
    OMail = nothing
    OApp = nothing

[ItsMe]

.. I want it to send automatically WITHOUT me hitting the send button...

SendObject has an option to send without viewing hte email. Here is a VBA example. The False at the end of docmd will bypass user interaction. Not sure if the macro will have ability to adjust this or not.

Dim strSubject As String
Dim strBody As String
Dim strReport As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
strSubject = "Insert Subject Line Here"
strBody = "Insert text to be displayed in the email body"
strReport = "rptEquip"
strTo = "you@gmail.com"
strCc = ""
strBcc = "me@gmail.com"
DoCmd.SendObject acSendReport, strReport, acFormatTXT, strTo, strCc, strBcc, strSubject, strBody, False

[Xipooo]

SendObject has an option to send without viewing hte email. Here is a VBA example. The False at the end of docmd will bypass user interaction. Not sure if the macro will have ability to adjust this or not.

Dim strSubject As String
Dim strBody As String
Dim strReport As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
strSubject = "Insert Subject Line Here"
strBody = "Insert text to be displayed in the email body"
strReport = "rptEquip"
strTo = "you@gmail.com"
strCc = ""
strBcc = "me@gmail.com"
DoCmd.SendObject acSendReport, strReport, acFormatTXT, strTo, strCc, strBcc, strSubject, strBody, False

Yeah, that's probably simpler. I always seem to forget there's some Access shortcut out there that can do most of what I write up objects to do. C# and VB.NET will do that to a man.

[kvn5x4]

SendObject has an option to send without viewing hte email. Here is a VBA example. The False at the end of docmd will bypass user interaction. Not sure if the macro will have ability to adjust this or not.

I tried the code attached and it was doing the same thing as the macro. It was requiring me to hit the send button. I tried Xipoo's code and it worked just fine, thank you very much for the help!!!!!

[kvn5x4]

I'd drop doing it in a Macro and do it in VBA instead.

I haven't tested this, but I think it should work..

The code worked perfectly, thank you so much! Any chance either of you could provide help with the batch file as well? I made the modifications to reference the VBA code, but when I run it nothing happens.

[ItsMe]

I don't know if it is simpler. Just trying to answer the OP's comment about not clicking the "Send" button. There may be a way to use Docmd.SendObject in the macro that will satisfy their need... as Alan suggested.

Also, I just noticed that my example shows acFormatTXT. OP may want to use acFormatPDF if they are inclined to go VBA and docmd

[ItsMe]

The code worked perfectly, thank you so much! Any chance either of you could provide help with the batch file as well? I made the modifications to reference the VBA code, but when I run it nothing happens.

the false at the end should bypass user interface with mail object.
DoCmd.SendObject acSendReport, strReport, acFormatTXT, strTo, strCc, strBcc, strSubject, strBody, False

You could create .vbs file and use windows scheduler to open your DB.

Save this as .vbs and have your scheduler open it. YOur scheduler can then terminate its instance by adjusting settings in the scheduler.


for Access 2010 version

Option Explicit
Dim oShell
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"" ""C:\Folder\AccessFileName.accdb"""

[kvn5x4]

You could create .vbs file and use windows scheduler to open your DB.

Save this as .vbs and have your scheduler open it. YOur scheduler can then terminate its instance by adjusting settings in the scheduler.


for Access 2010 version

Option Explicit
Dim oShell
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"" ""C:\Folder\AccessFileName.accdb"""

Thank you again. I tried the code and saved it as you mentioned. MS Access opens but the VBA code does not run. Sorry to bother, I am just getting into all this Access stuff and my boss is expecting a lot out of me hahaha.

[Xipooo]

Thank you again. I tried the code and saved it as you mentioned. MS Access opens but the VBA code does not run. Sorry to bother, I am just getting into all this Access stuff and my boss is expecting a lot out of me hahaha.

Have you set up an Event Trigger?

On whichever button you're clicking on, go to the Event tab, click on the ellipsis next to "On Click" and Select "Code Builder". Inside of the sub created by Access, copy the code.

Don't forget to change the "ReportName" to whatever the name of your report is.

[ItsMe]

Thank you again. I tried the code and saved it as you mentioned. MS Access opens but the VBA code does not run. Sorry to bother, I am just getting into all this Access stuff and my boss is expecting a lot out of me hahaha.

The code provided is VB script. Copy the code and paste it into a text file. Save the text file. change the extension to .VBS. Then, open your task scheduler or whatever it is called in windows. Use the scheduler wizard to create a new task (or whatever). Point the wizard towards the new .vbs file and adjust settings as needed.

[June7]

To run code automatically when Access opens, needs to be either:

1. autoexec macro that calls email procedure

2. code behind a form that is set to open by default when Access opens, use form Open event with email code or calls email procedure

[trevor40]

I use this to send emails to selected emplyees with selected multiple attachments , comment out what you dont need.

This line sends the email al by it's self, comment out and it gets displayed
BUT outlook must be setup yo auto send as well!
###############################
' SendKeys "%{s}", True
###############################


Set db = CurrentDb
Set rs = db.OpenRecordset("Employees")
rs.MoveFirst
Do While Not rs.EOF
If rs![Auto Receive Tracking Emails] = True Then
ttt = rs![E-Mail Address]
mail_to_list = mail_to_list + ttt & " ' ; '"
End If
rs.MoveNext
Loop
rs.Close
Set myItem = myOlApp.CreateItem(olmailItem)
Set myAttachments = myItem.Attachments
Set lst = Me!filelist
With myItem
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
ttt = lst.ItemData(vItem)
myItem.Attachments.Add ttt
End If
Next
Set lst = Nothing
myItem.To = mail_to_list
myItem.Subject = "Incoming Logistics Movement C/N # - " & Me.Consignment_Note_Number
myItem.Body = "Please find below, details for the incoming consignment." & vbCrLf & _
"Including any attachments if available" & "." & vbCrLf & vbCrLf & _
"This report is for Consignment # - " & Me.Consignment_Note_Number & "." & vbCrLf & vbCrLf & _
"Received On - " & Me.Date_Received & "." & " From " & Me.Courier & "." & vbCrLf & _
"Description - " & Me.Description_of_Goods & "." & vbCrLf & _
"Number of Items - " & Me.Number_of_Items & "." & " Total Weight - " & Me.Weight & "." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & _
"Addressed To - " & Me.addressed_to & "." & vbCrLf & _
"P/O or Invoice # Reference - " & Me.po_ref & "." & vbCrLf & vbCrLf & _
"Other Notes - " & Me.comments & "." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & _
"Thank you." & vbCrLf & _
"Logistics Department."
myItem.Display
SendKeys "%{s}", True
End With