.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
- Option Compare Database
- Option Explicit
- Public Function SendAnEMail(olSendTo As String, _
- olSubject As String, _
- olEMailBody As String, _
- olDisplay As Boolean, _
- Optional olCCLine As String, _
- Optional olBCCLine As String, _
- Optional olOnBehalfOf As String, _
- Optional olAtchs As String, _
- Optional SendAsHTML As Boolean) As Boolean
- On Error GoTo EH
- Dim olApp As Outlook.Application
- Dim olMail As Outlook.MailItem
- Dim strArray() As String
- Dim intAtch As Integer
- Set olApp = CreateObject("Outlook.Application")
- Set olMail = olApp.CreateItem(olMailItem)
- With olMail
- .To = olSendTo
- .Subject = olSubject
- If SendAsHTML Then
- .BodyFormat = olFormatHTML
- .HTMLBody = olEMailBody
- Else
- .Body = olEMailBody
- End If
- .CC = olCCLine
- .BCC = olBCCLine
- .SentOnBehalfOfName = olOnBehalfOf
- strArray = Split(olAtchs, "%Atch")
- For intAtch = 0 To UBound(strArray)
- If FileExists(strArray(intAtch)) Then _
- .Attachments.Add strArray(intAtch)
- Next intAtch
- If olDisplay Then
- .Display
- Else
- .Send
- End If
- End With
- Set olMail = Nothing
- Set olApp = Nothing
- SendAnEMail = True
- Exit Function
- EH:
- MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
- "Error: " & Err.Number & vbCrLf & _
- "Description: " & Err.Description & vbCrLf & vbCrLf & _
- "Please contact your Database Administrator.", vbCritical, "WARNING!"
- SendAnEMail = False
- Exit Function
- End Function
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
- Public Function FileExists(ByVal strFile As String, _
- Optional bFindFolders As Boolean) _
- As Boolean
- 'Purpose: Return True if the file exists, even if it is hidden.
- 'Arguments: strFile: File name to look for. Current directory searched if
- ' no path included.
- ' bFindFolders: If strFile is a folder, FileExists() returns False
- ' unless this argument is True.
- 'Note: Does not look inside subdirectories for the file.
- 'Author: Allen Browne. http://allenbrowne.com June, 2006.
- Dim lngAttributes As Long
- If Not (IsNull(strFile) Or strFile = "") Then
- 'Include read-only files, hidden files, system files.
- lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
- If bFindFolders Then
- 'Include folders as well.
- lngAttributes = (lngAttributes Or vbDirectory)
- Else
- 'Strip any trailing slash, so Dir does not look inside the folder.
- Do While Right$(strFile, 1) = "\"
- strFile = Left$(strFile, Len(strFile) - 1)
- Loop
- End If
- 'If Dir() returns something, the file exists.
- On Error Resume Next
- FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
- Else
- FileExists = False
- End If
- End Function
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
- Private Sub SendMail_Click()
- On Error GoTo EH
- Dim strSendTo As String
- Dim strSubject As String
- Dim strEMailBody As String
- Dim strCCLine As String
- Dim strBCCLine As String
- Dim strOnBehalfOf As String
- Dim strAtchs As String
- strSendTo = "Orders@PizzaGuy.biz"
- strSubject = "I Want a Pizza"
- strEMailBody = "I want a pizza <B>NOW</B>!!!"
- strCCLine = "MyBuddy@email.net"
- strBCCLine = "MyEnemy@email.net"
- strOnBehalfOf = "CEO@BigBusiness.org"
- strAtchs = "C:\File.pdf" & _
- "%Atch" & _
- "C:\Another File.pdf"
- 'Generate and Display the E-Mail
- Call SendAnEMail(olSendTo:=strsendto, _
- olSubject:=strSubject, _
- olEMailBody:=strEMailBody, _
- olDisplay:=True, _
- olCCLine:=strCCLine, _
- olBCCLine:=strBCCLine, _
- olOnBehalfOf:=strOnBehalfOf, _
- olAtchs:=strAtchs, _
- SendAsHTML:=True)
- Exit Sub
- EH:
- MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
- "Error: " & Err.Number & vbCrLf & _
- "Description: " & Err.Description & vbCrLf & vbCrLf & _
- "Please contact your Database Administrator.", vbCritical, "WARNING!"
- Exit Sub
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!