VBA example - Microsoft Access-e-mail with Outlook

Send e-mail form with Outlook

e-mail form

To create a send e-mail form program with Ms. Access 2007 and Outlook Automation object as show above, you need to open Ms. Access 2007 application database and create a form and name it sendemail. On this form, you will drop four fields of SentItems table(Recipient,To_email,Subject,Body), three command buttons(Attach,Send, New), and one textbox to display selected attached file. You also need to add Microsoft Outlook 12.0 Object Library from Tools menu. After naming these controls (you may name them as used in the code below. Otherwise, it doesn't work), use the following code:

Option Compare Database
Option Explicit


Private Sub Form_Load()
DoCmd.LockNavigationPane (True)
End Sub

Private Sub cmdattach_Click()
'clear textbox
Me.txtattach = ""

Dim fdialog As Office.FileDialog
Dim fs As Variant
'create filedilog object
Set fdialog = Application.FileDialog(msoFileDialogFilePicker)

With fdialog
'add filters
.Filters.Add "Ms.Access Files", "*.mdb; *.acced; *.*", 1
'disable multi selection
.AllowMultiSelect = False
If .Show Then
fs = .SelectedItems(1)
If fs <> "" Then
Me.txtattach = fs 'add to textbox

End If
End If
End With
'clear filedialog object
Set fdialog = Nothing

End Sub

Private Sub cmdsendmail_Click()
On Error Resume Next
Dim olobject As Outlook.Application
Set olobject = CreateObject("Outlook.Application")
Dim olmsg As Outlook.MailItem
Set olmsg = olobject.CreateItem(olmailitem)
With olmsg
Dim olre As Outlook.Recipient
Set olre = .Recipients.Add(Me![To_email])
olre.Type = olTo
.Subject = Me![Subject]
.Body = Me![Body]
.Attachments.Add CStr(Me!txtattach)
End With

Set olobject = Nothing
Set olmsg = Nothing
Set olre = Nothing

End Sub

Private Sub cmdnewmail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.txtattach = ""
End Sub


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.