danita.moon
New member
- Local time
- Today, 05:01
- 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.
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 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
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
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
C:\Users\Trevor G\AppData\Roaming\Microsoft\Signatures\Trevor.txt
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
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.
I would ask what have you tried so far for both of your questions. Can you show the additional code you have created.
Sub CommandButton1_Click()
Call sendForApproval1
End Sub
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.