Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rating: Thread Rating: 15 votes, 5.00 average. Display Modes
Old 06-18-2012, 03:56 PM   #1
danita.moon
Newly Registered User
 
Join Date: Jun 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
danita.moon is on a distinguished road
Exclamation Access 2010 Email Macro

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.

danita.moon is offline   Reply With Quote
Old 06-21-2012, 06:55 AM   #2
Trevor G
AWF - Happy to help
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,326
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Access 2010 Email Macro

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
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2013
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
The Following User Says Thank You to Trevor G For This Useful Post:
znxm0i (05-05-2015)
Old 06-24-2012, 06:05 PM   #3
Sue Azzubair
Newly Registered User
 
Join Date: Jun 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Sue Azzubair is on a distinguished road
Re: Access 2010 Email Macro

Hi -
Can you send a copy of a report from Access 2010 without using microsoft OUtlook?

Thanks
sue

Sue Azzubair is offline   Reply With Quote
Old 06-27-2012, 12:32 AM   #4
Trevor G
AWF - Happy to help
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,326
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Access 2010 Email Macro

Sue welcome to the Forum,

Do you use a specific email system, and if so what is it?
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2013
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 06-27-2012, 03:14 AM   #5
davisbroen21
Newly Registered User
 
Join Date: Jun 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
davisbroen21 is on a distinguished road
Re: Access 2010 Email Macro

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
davisbroen21 is offline   Reply With Quote
Old 06-30-2012, 03:52 AM   #6
Sue Azzubair
Newly Registered User
 
Join Date: Jun 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Sue Azzubair is on a distinguished road
Red face Re: Access 2010 Email Macro

[QUOTE=Trevor G;1167105]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
Sue Azzubair is offline   Reply With Quote
Old 07-02-2012, 12:13 AM   #7
Trevor G
AWF - Happy to help
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,326
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Access 2010 Email Macro

Look at this link it will give you the sample code you need.

http://www.rondebruin.nl/cdo.htm

__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2013
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 05-05-2015, 02:34 PM   #8
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

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.


Quote:
Originally Posted by Trevor G View Post
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
znxm0i is offline   Reply With Quote
Old 05-06-2015, 05:19 AM   #9
Trevor G
AWF - Happy to help
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,326
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Access 2010 Email Macro

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.

Quote:
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.

Quote:
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.

Quote:
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
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2013
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 05-11-2015, 10:43 AM   #10
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

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 is offline   Reply With Quote
Old 05-11-2015, 10:50 AM   #11
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

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 is offline   Reply With Quote
Old 05-11-2015, 12:36 PM   #12
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

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?



Quote:
Originally Posted by Trevor G View Post
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.
znxm0i is offline   Reply With Quote
Old 05-11-2015, 10:11 PM   #13
Trevor G
AWF - Happy to help
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,326
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Access 2010 Email Macro

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 am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2013
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 05-12-2015, 02:52 AM   #14
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

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\Signa tures\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 is offline   Reply With Quote
Old 05-12-2015, 07:15 AM   #15
znxm0i
Newly Registered User
 
Join Date: May 2015
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
znxm0i is on a distinguished road
Re: Access 2010 Email Macro

Quote:
Originally Posted by Trevor G View Post
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 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
LookupRecord Macro Enable EMail - Access 2010 Web Database (No VBA; Must be done in M gdgonzal Macros 1 06-08-2012 10:59 PM
Email from Access 2010 runtime / Server 2008 without an email client Cloyce Modules & VBA 0 05-15-2012 10:08 AM
Send Email macro/button (MS Access 2010) mac300 Macros 0 12-28-2011 12:27 AM
VBA for MS Access 2010 | Checking for valid email only when an email is entered Avionicx Modules & VBA 17 10-05-2011 12:05 PM
Question ms access 2010 email advice PNGBill General 5 07-17-2011 05:13 PM




All times are GMT -8. The time now is 12:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World