How to use automation to send ms outlook mail using Excel VBA
Hi Dr. Takyar,
I would like to thank you for your insightful trainings you have available. I would like your assistance in writing this macro for a user form I’ve created.
1).What would be the macro code to receive an email notification once the user clicks the submit button?
2).How to make certain fields required fields?
3).How can I make the captured data not visible to the user?
Thanks in advance!
The 2nd and 3rd questions are answered here:
Ensure data is entered
How to hide rows
Protecting Worksheets Using VBA
The complete code is given below:
Sub SendMail(Optional AttachmentPath)
Dim appOutlook As Outlook.Application
Dim mitOutlookMsg As Outlook.MailItem
Dim recOutlookRecip As Outlook.Recipient
Dim attOutlookAttach As Outlook.Attachment
' Step 1: Initialize an Outlook session.
Set appOutlook = CreateObject("Outlook.Application")
' Step 2: Create a new message.
Set mitOutlookMsg = appOutlook.CreateItem(olMailItem)
' Step3: Add the To recipient(s) to message.
Set recOutlookRecip = .Recipients.Add("Harry Mitchell")
recOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set recOutlookRecip = .Recipients.Add("Laura Jones")
recOutlookRecip.Type = olCC
' Set valid properties like Subject, Body, and Importance of the message.
.Subject = "Email Automation with MS Outlook"
.Body = "Testing..." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments
If Not IsMissing(AttachmentPath) Then
Set attOutlookAttach = .Attachments.Add(AttachmentPath)
' Resolve every Recipient's name
For Each recOutlookRecip In .Recipients
If Not recOutlookRecip.Resolve Then
Set mitOutlookMsg = Nothing
Set appOutlook = Nothing
Private Sub CommandButton1_Click()
Private Sub CommandButton2_Click()
Dim eRow As Long
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If TextBox1.Text = "" Then
MsgBox "Name cannot be blank"
Cells(eRow, 1) = TextBox1.Text
Private Sub CommandButton3_Click()
Private Sub CommandButton4_Click()
TextBox1.Text = ""
Private Sub CommandButton5_Click()
Private Sub UserForm_Initialize()
Watch the training video below to see how the process of complete automation of sending an email message with attachment through MS Outlook using VBA is implemented: