How to use automation to send ms outlook mail using Excel VBA

Home More Videos

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:
Option Explicit
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)
With mitOutlookMsg
' 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)
End If
' Resolve every Recipient's name
For Each recOutlookRecip In .Recipients
If Not recOutlookRecip.Resolve Then
End If
End With
Set mitOutlookMsg = Nothing
Set appOutlook = Nothing
End Sub
Private Sub CommandButton1_Click()
End Sub
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"
End If
Cells(eRow, 1) = TextBox1.Text
End Sub
Private Sub CommandButton3_Click()
End Sub
Private Sub CommandButton4_Click()
TextBox1.Text = ""
End Sub
Private Sub CommandButton5_Click()
End Sub
Private Sub UserForm_Initialize()
End Sub

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:

Watch the video on youtube

More Reading: Sending Emails Automatically Using VBA