473,288 Members | 1,726 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,288 developers and data experts.

Sending E-Mails via Outlook - One Developer's Approach

twinnyfo
3,653 Expert Mod 2GB
When using MS Access to manage data, it is often helpful to use the automation features of Access to generate e-mails and send them to your customers. If you happen to be sending an Access Object (like an MS Access-generated Report), you can use the built-in .SendObject Method--but many times, we just want (or need) to generate related correspondence, based upon the work you are doing in the Database.

Below, I will describe the method I use for sending e-mail via MS Outlook. Since that is the e-mail application we use at work, and since it is a standard desktop application for many of us, this is a great place to start. If you use a different e-mail application (not a web-mail service) there may be ways to modify this article's code in order to accommodate, but my focus will be on MS Outlook as the e-mail application.

First, it is possible to add lines of code that generate an e-mail to all your Forms, add attachments as necessary and then properly format things all nice and neat. However, if we do this, we find ourselves "re-inventing the wheel" every time we need to send an e-mail. From a developer's perspective, this is a waste of bits and bytes. A "better" approach may be to standardize the e-mail-sending procedure in a globally accessible Function which does all that work for you. Then, all you have to do is call that Funciton, sending the necessary arguments to it.

Here's how we do it:

First, in a separate, standalone VBA Module, create your e-mail function:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function SendAnEMail(olSendTo As String, _
  5.                             olSubject As String, _
  6.                             olEMailBody As String, _
  7.                             olDisplay As Boolean, _
  8.                    Optional olCCLine As String, _
  9.                    Optional olBCCLine As String, _
  10.                    Optional olOnBehalfOf As String, _
  11.                    Optional olAtchs As String, _
  12.                    Optional SendAsHTML As Boolean) As Boolean
  13. On Error GoTo EH
  14.     Dim olApp       As Outlook.Application
  15.     Dim olMail      As Outlook.MailItem
  16.     Dim strArray()  As String
  17.     Dim intAtch     As Integer
  18.  
  19.     Set olApp = CreateObject("Outlook.Application")
  20.     Set olMail = olApp.CreateItem(olMailItem)
  21.     With olMail
  22.         .To = olSendTo
  23.         .Subject = olSubject
  24.  
  25.         If SendAsHTML Then
  26.             .BodyFormat = olFormatHTML
  27.             .HTMLBody = olEMailBody
  28.         Else
  29.             .Body = olEMailBody
  30.         End If
  31.  
  32.         .CC = olCCLine
  33.         .BCC = olBCCLine
  34.         .SentOnBehalfOfName = olOnBehalfOf
  35.         strArray = Split(olAtchs, "%Atch")
  36.  
  37.         For intAtch = 0 To UBound(strArray)
  38.             If FileExists(strArray(intAtch)) Then _
  39.                 .Attachments.Add strArray(intAtch)
  40.         Next intAtch
  41.  
  42.         If olDisplay Then
  43.             .Display
  44.         Else
  45.             .Send
  46.         End If
  47.  
  48.     End With
  49.     Set olMail = Nothing
  50.     Set olApp = Nothing
  51.  
  52.     SendAnEMail = True
  53.  
  54.     Exit Function
  55. EH:
  56.     MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
  57.         "Error: " & Err.Number & vbCrLf & _
  58.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  59.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  60.     SendAnEMail = False
  61.     Exit Function
  62. End Function
You will notice that it is not very complicated. We are just creating an e-mail in the same way that you would anywhere else. However, now this function is accessible from anywhere in your project and all you need to do is call it in order to create the e-mail. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.

You may notice that there is one (and only one) argument for attachments. However, you can add multiple attachments from the calling code. Just keep in mind that you need to separate each attached file with the text "%Atch".

You may also notice a reference to another public function named FileExists(). This ensures you are not trying to attach a file that doesn't exist. I think there are about as many versions of such a function as there are programmers, but here is my version (that I originally stole from Allen Browne--props where props are due).

Expand|Select|Wrap|Line Numbers
  1. Public Function FileExists(ByVal strFile As String, _
  2.                            Optional bFindFolders As Boolean) _
  3.                            As Boolean
  4. 'Purpose:   Return True if the file exists, even if it is hidden.
  5. 'Arguments: strFile: File name to look for. Current directory searched if
  6. '                    no path included.
  7. '           bFindFolders: If strFile is a folder, FileExists() returns False
  8. '                         unless this argument is True.
  9. 'Note:      Does not look inside subdirectories for the file.
  10. 'Author:    Allen Browne. http://allenbrowne.com June, 2006.
  11.     Dim lngAttributes As Long
  12.  
  13.     If Not (IsNull(strFile) Or strFile = "") Then
  14.         'Include read-only files, hidden files, system files.
  15.         lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
  16.  
  17.         If bFindFolders Then
  18.             'Include folders as well.
  19.             lngAttributes = (lngAttributes Or vbDirectory)
  20.         Else
  21.             'Strip any trailing slash, so Dir does not look inside the folder.
  22.             Do While Right$(strFile, 1) = "\"
  23.                 strFile = Left$(strFile, Len(strFile) - 1)
  24.             Loop
  25.         End If
  26.         'If Dir() returns something, the file exists.
  27.         On Error Resume Next
  28.         FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
  29.     Else
  30.         FileExists = False
  31.     End If
  32.  
  33. End Function
This function can go in the same module, if you wish.

Now, you are ready to "Rock 'n' Roll"!

In your Form, if you want to send an e-mail via clicking a button, here is all you have to do:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendMail_Click()
  2. On Error GoTo EH
  3.     Dim strSendTo     As String
  4.     Dim strSubject    As String
  5.     Dim strEMailBody  As String
  6.     Dim strCCLine     As String
  7.     Dim strBCCLine    As String
  8.     Dim strOnBehalfOf As String
  9.     Dim strAtchs      As String
  10.  
  11.     strSendTo = "Orders@PizzaGuy.biz"
  12.     strSubject = "I Want a Pizza"
  13.     strEMailBody = "I want a pizza <B>NOW</B>!!!"
  14.     strCCLine = "MyBuddy@email.net"
  15.     strBCCLine = "MyEnemy@email.net"
  16.     strOnBehalfOf = "CEO@BigBusiness.org"
  17.     strAtchs = "C:\File.pdf" & _
  18.                "%Atch" & _
  19.                "C:\Another File.pdf"
  20.  
  21.     'Generate and Display the E-Mail
  22.     Call SendAnEMail(olSendTo:=strsendto, _
  23.                      olSubject:=strSubject, _
  24.                      olEMailBody:=strEMailBody, _
  25.                      olDisplay:=True, _
  26.                      olCCLine:=strCCLine, _
  27.                      olBCCLine:=strBCCLine, _
  28.                      olOnBehalfOf:=strOnBehalfOf, _
  29.                      olAtchs:=strAtchs, _
  30.                      SendAsHTML:=True)
  31.  
  32.     Exit Sub
  33. EH:
  34.     MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
  35.         "Error: " & Err.Number & vbCrLf & _
  36.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  37.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  38.     Exit Sub
Obviously, the "Send of Behalf of" feature will only work if you are on a MS Exchange server that allows such things. Additionally, many corporate e-mail servers don't allow for sending directly from outside of Outlook; hence, I just default to displaying the e-mail.

And.....

That's about it. It makes sending e-mail a breeze--especially in our office, in which we send out approximately 1,000 various e-mails for each of our six annual projects. Yeah, this saves me a ton of programming time.

If any o' all y'all have any recommended improvements, I am all ears!

hope this hepps!
Aug 29 '18 #1
4 4557
zmbd
5,501 Expert Mod 4TB
. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.
One can also use late-binding to avoid setting the reference. This is most useful when distributing your application to other clients or when upgrading from one version of Office to another (or downgrading).

For a further discussion on Application Automation:
home > topics > microsoft access / vba > insights > application automation
Aug 31 '18 #2
ADezii
8,834 Expert 8TB
zmbd brings up a good point regarding Late Binding, but why not have the best of both worlds by using Runtime Conditional Compilation Constants?
Expand|Select|Wrap|Line Numbers
  1. #Const conEARLY_BIND = True
Expand|Select|Wrap|Line Numbers
  1. Public Function SendAnEMail(olSendTo As String, _
  2.                              olSubject As String, _
  3.                              olEMailBody As String, _
  4.                              olDisplay As Boolean, _
  5.                     Optional olCCLine As String, _
  6.                     Optional olBCCLine As String, _
  7.                     Optional olOnBehalfOf As String, _
  8.                     Optional olAtchs As String, _
  9.                     Optional SendAsHTML As Boolean) As Boolean
  10. On Error GoTo EH
  11. Dim strArray()  As String
  12. Dim intAtch     As Integer
  13. Const conEMAIL_ITEM = 0     'If using Late Binding cannot use olMailItem Intrinsic Constant
  14.  
  15. #If conEARLY_BIND Then
  16.   Dim olApp As Outlook.Application
  17.   Set olApp = New Outlook.Application
  18.   Dim olMail As Outlook.MailItem
  19.   Set olMail = olApp.CreateItem(olMailItem)
  20. #Else
  21.   Dim olApp  As Object
  22.   Dim olMail As Object
  23.   Set olApp = CreateObject("Outlook.Application")
  24.   Set olMail = olApp.CreateItem(0)
  25. #End If
  26. '************************ CODE HAS INTENTIONALLY BEEN OMITTED BELOW ************************
Nov 21 '18 #3
DJRhino1175
221 128KB
I like this approach, the only other upgrade would be to have it pull the email address from a table so you can send to more than one person with out listing them all in the code itself. I have this in one of my database that I put together from the help in the group of awesome programmers. I will post the code if anyone would like to see it tomorrow.
Nov 28 '18 #4
twinnyfo
3,653 Expert Mod 2GB
DJ,

Keep in mind that this procedure simply shows the execution of the process. So, it doesn’t matter how you generate the string for the addressees. As long as you build the strings properly, you can send an e-mail from anywhere at any time. Your list of addressees can be quite large, too, as long as each addressee is separated by a semi colon (“;”)—this is my default in Outlook.

The heart of this procedure is to allow you to simply build strings and send an e-mail using a User-Defined procedure, rather than re-creating the wheel every time you have to send a message. This cuts down on the clutter of your projects and allows you to focus more time on development.

Thanks for the input!
Nov 29 '18 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: VB Programmer | last post by:
My company has it's own webserver, which is going to host our ASP.NET web application. We want the website to be able to send out emails. 1. What do I need on the server so that it has the...
2
by: Mr. x | last post by:
Hello, I am sending emails with Hebrew contents. When receiving emails - I cannot see the Hebrew characters (it is not outlook express configuration, because when receiving emails from friends -...
0
by: Ryan | last post by:
Outlook problem with encoding characters when sending emails in asp.net I'm putting this out here incase someone has the same problem I did.. I had a problem with German characters not showing...
3
by: Arek | last post by:
Hey, I have a question, what are the possibilities of sending emails using ASP.net. (and VB.net) What I think is that user can send automatic reminder if he check box on the form and submit. Do...
3
by: A | last post by:
Hi all! I would like to ask a question on sending emails... I have a web application that requires sending emails using email templates. The templates that I've made are separate HTML files...
2
by: Jodye Roebuck | last post by:
I have a program that's been automatically generating and sending emails flawlessly. My clients workstation had an automatic Microsoft security update done on it.. and now it's bombing on this...
7
by: mlevit | last post by:
Hi, I send out emails through MS Access via SendObject. I would like to know if there is a way of getting rid of the Outlook Security Popup that comes up every time an email is about to be sent. ...
2
by: sam | last post by:
Hi, I made asp.net website. I send e-mail via asp.net page to receiver but receiver cannot get email. I use smartertool Server SMTP. Sending and receiving to localhost is working. Sending...
4
Ali Rizwan
by: Ali Rizwan | last post by:
Hello all, I have made a web page, Now i want to add some features like sending emails. I want to use HTML code to send emails. Email text will be retrieved by combining the text of two text...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.