Automatically Send Emails Using Excel!
If you can automatically send emails, it seems like it would be a significant time-saver. Based on this idea, I considered a program as outlined below. It is possible with VisualStudio, Excel, and Outlook.
Settings on the Visual Basic Side
1. Project > Add Reference > Check the "Microsoft Excel 16.0 Object Library" in the COM tab.
2. Project > Add Reference > Check the "System.Net.Http" in the Assemblies tab.
3. At the top of the VB procedure, write the following:
Imports Microsoft.Office.Interop
The required information in advance is:
◆ Email content
◆ Recipient's email address
◆ Recipient's name
◆ Folder path where the "NOTEPAD2" text file is saved
◆ "NOTEPAD2" text file
◆ Your email address (set as info@ninproducts.net in this case)
◆ Bcc email addresses
◆ Excel file named "Automail"
◆ Folder path where the Excel file is saved
The code in Visual Studio is as follows:
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim memo1 As String = ""
Dim mailaddress As String = ""
Dim customername As String = ""
Dim myfolder As String = ""
mailaddress = "xxxxxxxx@xxxxxxx.xx.xx"
customername = "Test of Automatic Email Sending System"
memo1 = mailaddress & vbNewLine
memo1 = memo1 & "The product has been shipped [Shipping Number Notification]" & vbNewLine
' Below is the BCC email address
memo1 = memo1 & "info@ninproducts.net" & vbNewLine
memo1 = memo1 & "The product has been shipped [Shipping Number Notification]" & vbNewLine
memo1 = memo1 & vbNewLine
memo1 = memo1 & customername & vbNewLine
memo1 = memo1 & "This is Ninnin Products." & vbNewLine
memo1 = memo1 & "The following product has been shipped from our company." & vbNewLine
memo1 = memo1 & "________________________________________________" & vbNewLine
myfolder = "C:\Users\lizlo\Desktop\Check This Out Automatic Email Sending"
' Paste to Notepad
Dim textfile As IO.StreamWriter
textfile = New IO.StreamWriter(myfolder & "\NOTEPAD2")
textfile.Write(memo1)
textfile.WriteLine()
textfile.Close()
Call openexcel2()
'-------------------------------------------------------------
' Automatically open Excel. Without showing the window.
End Sub
Private Sub openexcel2()
Dim excelApp As New Excel.Application
Dim excelplace as string = ""
Excelplace = "C:\Users\lizlo\Desktop\Check This Out Automatic Email Sending\automail.xlsm"
Dim workbook As Excel.Workbook = excelApp.Workbooks.Open(excelplace)
' Set it not to display
excelApp.Visible = False
excelApp.DisplayAlerts = False
' Open the file
excelApp.Quit()
' Release the object
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
'sheet = Nothing
workbook = Nothing
excelApp = Nothing
' Force garbage collection
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
End Class
Excel Settings
Press Alt+F11, go to Microsoft Visual Basic for Applications > Tools > References, and check the following:
Visual Basic for Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Outlook 16.0 Object Library
Check Microsoft Outlook 16.0 Object Library
The following is the Visual Basic for Applications code inside Excel. Note that the declaration is in the "Workbook_Open()" section.
Private Sub Workbook_Open()
'---------------------------------------------------------
' Macro to automatically send email
' Last revised: 2025/04/18
'---------------------------------------------------------
Dim filename As String
filename = ThisWorkbook.Path
ChDir filename
Workbooks.OpenText filename:=filename & "\Notepad2", _
Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=-45
'----------------------------------------------------
‘-----------------------------------------------------
‘ Create a sheet named "DATA"
Dim ws2 As Worksheet
Dim sheetName As String
sheetName = "DATA"
Set ws2 = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
Ws2.Name = sheetName
‘-----------------------------------------------------
Range("A1:A10000").Select
Selection.Copy
Windows("automail.xlsm").Activate
Sheets("DATA").Select
Range("A1").Select
ActiveSheet.Paste
' Program 2 | Sheet Setup
Dim ws As Worksheet
Set ws = Worksheets("DATA")
' Program 3 | Launch Outlook application
Dim outlookObj As Outlook.Application
Set outlookObj = CreateObject("Outlook.Application")
' Program 4 | Create Outlook email
Dim mymail As Outlook.MailItem
Set mymail = outlookObj.CreateItem(olMailItem)
'----------------------
' Data paste process
'----------------------
' Program 5 | Set up email information
mymail.BodyFormat = 3 ' Send email in rich text format
mymail.To = ws.Range("A1").Value ' To recipient
mymail.Subject = ws.Range("A2").Value ' Subject
mymail.BCC = ws.Range("A3").Value ' BCC recipient
' Not written yet for B4 onwards
' Program 6 | Set up email body
Dim mailbody As String
Dim p As Long
Dim content As String
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
Dim values As String
' Get last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Specify range
Set rng = Range("A4:A" & lastRow)
' Get values
For Each cell In rng
values = values & cell.Value & vbCrLf
Next cell
' Display in message box
' MsgBox values
mailbody = values
mymail.Body = mailbody & vbCrLf & vbCrLf
' Program 7 | Set up file attachment if needed
Dim attachedfile As String
'attachedfile = ThisWorkbook.Path & "\" & ws.Range("B9").Value
'If Not attachedfile = "" Then
'mymail.Attachments.Add Source:=attachedfile
'End If
' Program 8 | Show email
'mymail.Display ' Show email (set to display to prevent accidental sending)
' Note: mymail.Send will automatically send the email
' Program 9 | Save email
'mymail.Save ' Save as draft (not saving in this case, so add single quote)
' Program 10 | Automatically send email
mymail.Send
' Program 11 | Release objects
Set outlookObj = Nothing
Set mymail = Nothing
' Program 12 | End program
Application.DisplayAlerts = False
Application.Quit
End Sub