Access 2010 Email Macro (1 Viewer)

danita.moon

New member
Local time
Today, 12:54
Joined
Jun 19, 2012
Messages
1
I'm trying to write a program which will go through a database and send an email with a specific message to each email automatically.
 

Trevor G

Registered User.
Local time
Today, 19:54
Joined
Oct 1, 2009
Messages
2,341
Welcome to the forum,

The following code will send email via Outlook. You have to set the reference to use Outlook. In your database use Alt + F11 to go into the VBA screen then click the Tools Menu and then References, search down the list for Microsoft Outlook 14 Object Library and click the box. Then close the dialog box next goto the Insert Menu and click Module, Copy and paste the code into it then you have a working option to run some code to send an email, if you mean something different like to a list of people then you will have to post back with some clear information.

Code:
Sub sendForApproval1()
'*************************************************
'VBA Code created by Trevor G July 2010
'Contact via mobile

'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail

    .To = "trevor.g@emailaddress.com"
    .Subject = "Sample Database - Access Request"
    .Body = "Please can you provide access to the Sample Database system." & vbCr & vbCr & _
    "The required details are as follows my Full Name is:  " & vbCr & _
    "My User ID is: " & vbCr & _
    "My Computer Name is: "

    .Display 'Change to Send once you are happy this is working for you.

End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
 

Sue Azzubair

New member
Local time
Tomorrow, 06:24
Joined
Jun 23, 2012
Messages
2
Hi -
Can you send a copy of a report from Access 2010 without using microsoft OUtlook?
:banghead:
Thanks
sue
 

Trevor G

Registered User.
Local time
Today, 19:54
Joined
Oct 1, 2009
Messages
2,341
Sue welcome to the Forum,

Do you use a specific email system, and if so what is it?
 

davisbroen21

New member
Local time
Today, 12:54
Joined
Jun 27, 2012
Messages
2
The afterward cipher will forward email via Outlook. You accept to set the advertence to use Outlook. In your database use Alt + F11 to go into the VBA awning again bang the Tools Menu and again References, seek down the account for Microsoft Outlook 14 Object Library and bang the box. Again abutting the chat box next goto the Insert Menu and bang Module, Copy and adhesive the cipher into it again you accept a alive advantage to run some cipher to forward an email, if you beggarly something altered like to a account of humans again you will accept to column aback with some bright information.

----------------------------
the seven deadly sins
 

Sue Azzubair

New member
Local time
Tomorrow, 06:24
Joined
Jun 23, 2012
Messages
2
Sue welcome to the Forum,

Do you use a specific email system, and if so what is it?

Thank you for reply - took my awhile to work how to reply ..... it said reply with a quote - took the quot out -

I use gmail - would be great if you could help -
sue
:D
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Trevor,

First, let me begin by saying "thank you, thank you, thank you" for providing workable code which I was able to implement for my situation. You do not know how long I have been scouring the internet searching for a solution.

I do have a few additional questions for you regarding this particular code:

1: how can I automatically attach a pdf document which resides on my local hard drive
2: how can I get the subject to automatically contain the date the email is being sent (i.e., "5-May 2015 Daily Report")
3. How can I get the generated outlook email to automatically also contain my signature block

Thank you again and in advance for all your help in this matter.


Welcome to the forum,

The following code will send email via Outlook. You have to set the reference to use Outlook. In your database use Alt + F11 to go into the VBA screen then click the Tools Menu and then References, search down the list for Microsoft Outlook 14 Object Library and click the box. Then close the dialog box next goto the Insert Menu and click Module, Copy and paste the code into it then you have a working option to run some code to send an email, if you mean something different like to a list of people then you will have to post back with some clear information.

Code:
Sub sendForApproval1()
'*************************************************
'VBA Code created by Trevor G July 2010
'Contact via mobile

'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail

    .To = "trevor.g@emailaddress.com"
    .Subject = "Sample Database - Access Request"
    .Body = "Please can you provide access to the Sample Database system." & vbCr & vbCr & _
    "The required details are as follows my Full Name is:  " & vbCr & _
    "My User ID is: " & vbCr & _
    "My Computer Name is: "

    .Display 'Change to Send once you are happy this is working for you.

End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
 

Trevor G

Registered User.
Local time
Today, 19:54
Joined
Oct 1, 2009
Messages
2,341
Welcome to the Forum,

I have added the extras you have asked for. First you need to add the Function above the code that runs the email. So copy this Function into another Module sheet.

Function GetBoiler(ByVal sFile As String) As String
'**** Created by someone else
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Next you would add and adapt this code. I have added the formatting and date to the subject line.

To add your signature you have to know where it is located. The method is to follow this string line to see if there signature is there. Use windows explorer and search for this changing your name details and the name of the signature block you have used. I have highlighted in red the parts you need to change.

C:\Users\Trevor G\AppData\Roaming\Microsoft\Signatures\Trevor.txt

The code to run is shown below again I have highlighted the parts you need to adjust including the file path and pdf document name.

Sub sendForApproval1()
'*************************************************
'VBA Code created by Trevor G May 2015
'Updated from previous version
'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
SigString = "C:\Users\Trevor G\AppData\Roaming\Microsoft\Signatures\Trevor.txt"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
With olMail
.To = "trevor.g@emailaddress.com"
.Subject = Format(Date, "dd-mmm-yyyy") & " Daily Report"
.Body = "Please can you provide access to the Sample Database system." & vbCr & vbCr & _
"The required details are as follows my Full Name is: " & vbCr & _
"My User ID is: " & vbCr & _
"My Computer Name is: " & vbCr & vbCr & Signature

.Attachments "The path and file name.pdf"
.Display 'Change to Send once you are happy this is working for you.
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Hi Trevor,

Thank you so very much for responding to my request and for modifying your code for my use. I apologize for the delay in responding, things have been soooo busy. I am attempting to use the code as instructed, however, I can't get it to work.

I took the first block of code you gave me called "Function GetBoiler" and placed it in a new module. I then took the "sendforApproval" code and modified it as instructed and left it in it's own separate module.

I then tried to run the "Function GetBoiler" code and it opens up the "sendforApproval" code from the other module and highlights the line ".Attachments" and gives a Compile Error: Invalid use of property.

What could I be doing wrong? I am using MS-Access 2010 & Outlook 2010
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Trevor,

Disregard my post.....I figured out how to fix it. I had to make it ".Attachments.Add"

Again, thank you so very much for helping me, I appreciate it immensely. I will let you know how everything turns out as I'm still working on this.
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Trevor,

Please help again. What I am trying to do now is not working....what am I overlooking?

Okay, so the code you modified for me is working and NOW what I'm trying to do is:

1. Export and output to pdf the daily ms-access report using "ExportWithFormatting" which is saved to my desktop.

2. I want both the two modules I've created containing the code you gave me to run via the above macro. I've tried to run them using the "RunCode" command within the macro calling on the module names as functions but it isn't working.

Where am I going wrong at?



Welcome to the Forum,

I have added the extras you have asked for. First you need to add the Function above the code that runs the email. So copy this Function into another Module sheet.



Next you would add and adapt this code. I have added the formatting and date to the subject line.

To add your signature you have to know where it is located. The method is to follow this string line to see if there signature is there. Use windows explorer and search for this changing your name details and the name of the signature block you have used. I have highlighted in red the parts you need to change.



The code to run is shown below again I have highlighted the parts you need to adjust including the file path and pdf document name.
 

Trevor G

Registered User.
Local time
Today, 19:54
Joined
Oct 1, 2009
Messages
2,341
I would ask what have you tried so far for both of your questions. Can you show the additional code you have created.
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Hi Trevor,

Thank you for responding so quickly. Here is is the code I have in my database so far:

First module named RptExtras:

Function GetBoiler(ByVal sFile As String) As String
'**** Created by someone else
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Second module named RptAutomation:

Sub sendForApproval1()
'*************************************************
'VBA Code created by Trevor G May 2015
'Updated from previous version
'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
SigString = "C:\Users\con04785\AppData\Roaming\Microsoft\Signatures\Nina.txt"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
With olMail
.To = "nina@email.com"
.Subject = Format(Date, "dd-mmm-yyyy") & " Daily Report"
.Body = "Attached is the latest report update." & vbCr & vbCr & _
"Thank you." & vbCr & _
"" & vbCr & _
"Regards" & Signature

.Attachments.Add "C:\Users\con04785\Desktop\Daily Protocol Status.pdf"
.Display 'Change to Send once you are happy this is working for you.
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub

Thirdly, I have a macro entitled “DailyProtocolReport” setup as follows:

ExportWithFormatting
Object Type: Report
Object Name: Daily Protocol Status
Output Format: PDF Format (*.pdf)
Output File: c:\Users\con04785\Desktop\
Auto Start: No
Template File: field is empty
Encoding: field is empty
Output Quality: Print

RunCode
Function Name: GetBoiler()

I initially applied your code to a test copy of the database to make sure it would work, and once I was able to see that it worked, I then placed the code in the production database. After placing the code in the production database, I ran the RptExtras code, it triggered the RptAutomation code and created the desired MS-Outlook email. BUT NOW things don’t work at all….instead the production database gives the error at the line beginning with “Dim olApp As Outlook.Application” and won’t move past this line. However, the test database is working with the code you provided, doesn’t give any errors, and does not contain any macros.

What could I possibly be doing wrong? It’s evident that I am not an access guru. I only know enough to maneuver my way around up to a point, and I’m sure I’m overlooking something simple.

Trevor, please help.
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
I would ask what have you tried so far for both of your questions. Can you show the additional code you have created.

Trevor,

I wanted to add too, that I figured out why I couldn't get the code to run past the "Dim 01App As Outlook.Application" line.....I had to set the reference for Outlook in the database, so things are running again now.

I still have been unable to understand how to get the vba code to run via macro and so I'm still searching he internet for instructions on how to do it. Although I'm not an Access guru, I do take the initiative to try and find answers to help myself understand.

But I would still like your help please Trevor if you can show me how to do it.

Thank you.
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Hello Trevor,

I just wanted to drop a line to let you know that I finally got the macro to work with the modules. I kept searching the internet for answers and someone was nice enough to tell me that I needed to change the Sub routine to a Function and then call that module, which in turn would call the GetBoiler function. Viola! now it runs like a charm.

Again, thank you so much for helping me get as far as I did. You've helped make the automation of running a daily report so much more easier for this Administrative Assistant and I couldn't be happier.

Take care!
 

Trevor G

Registered User.
Local time
Today, 19:54
Joined
Oct 1, 2009
Messages
2,341
Add a button to a form and then in the event tab select On Click the 3 dots and select Code Builder then in the code type in the name of the sub routine (between the Sub and End Sub lines) which is sendForApproval1

So something like this, when you add your button it might be called CommandButton1

Sub CommandButton1_Click()
Call sendForApproval1
End Sub
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Hi Trevor,

Thanks so much for the suggestion. The database macros/functions are all working as they should. The ONLY problem I have now is that I can't get it to run via Task Scheduler when I try to place a switch to activate the macro.

If I don't place a switch after the argument in Task Scheduler to open the database, the first part of my macro will run which outputs the pdf file to my desktop.

If I do place the /x macroname after the argument to open the database the code you provided me with WILL NOT run, instead I get an error message 2001 referencing the sendForApproval1() code.

Do you have any idea how I can get this to work? All I want to do is set this macro up to automatically run my reports each day whether I'm at my desk or not.

Thank you.
 

znxm0i

Registered User.
Local time
Today, 14:54
Joined
May 5, 2015
Messages
11
Hi Trevor,

I just wanted to take a moment to tell you again how thankful I am for all the help you provided me. I finally figured out on my own what was stopping MS-Task Scheduler from running my macros. Thanks to your code and MS-Task Scheduler, my Access macro automatically outputs a pdf report, and then creates and sends an MS-Outlook email daily containing the contents without any user intervention.

I hope others find your code useful. Thanks again!
 

amplitude

New member
Local time
Today, 12:54
Joined
Oct 1, 2017
Messages
3
I'm trying to write a program which will go through a database and send an email with a specific message to each email automatically.

Please i also need help with this code. I would like to be able to send the email using Gmail. Thank you.
 

Users who are viewing this thread

Top Bottom