outlook Archives - Quereact

Asked: July 31, 2022Computer programming

How to Send Email in Excel Using VBA?

Anonymous

Added an answer on July 31, 2022 at 3:22 pm

This answer was edited.

Hello, You need to give access Outlook with VBA, you need to activate the Microsoft Outlook Object Library. To activate it, go to Tools in the VBA editor > References. Activate Microsoft Outlook 16.0 Object Library : Then in a module use this subroutine: Sub sendAnEmail(ByRefRead more

Hello,

You need to give access Outlook with VBA, you need to activate the Microsoft Outlook Object Library.

To activate it, go to Tools in the VBA editor > References.

Activate Microsoft Outlook 16.0 Object Library :

Then in a module use this subroutine:

Sub sendAnEmail(ByRef DomainName As String, ByRef SendTo As String, ByRef EmailSubject As String, ByRef EmailBody As String)

    'refer to outlook application
    Dim EmailApp As Outlook.Application
    Dim Source As String
    'launch outlook application
    Set EmailApp = New Outlook.Application
    'refer to a new outlook email
    Dim EmailItem As Outlook.MailItem
    'create a new outlook email
    Set EmailItem = EmailApp.CreateItem(olMailItem)
    
    EmailItem.To = SendTo 'the email adress who the email is sent to
    EmailItem.Subject = EmailSubject 'write your email subject in this variable
    EmailItem.HTMLBody = EmailBody
    Source = ThisWorkbook.FullName
    'send the new email
    EmailItem.Send
End Sub

Sub sendAnEmail(ByRef DomainName As String, ByRef SendTo As String, ByRef EmailSubject As String, ByRef EmailBody As String)

'refer to outlook application

Dim EmailApp As Outlook.Application

Dim Source As String

'launch outlook application

Set EmailApp = New Outlook.Application

'refer to a new outlook email

Dim EmailItem As Outlook.MailItem

'create a new outlook email

Set EmailItem = EmailApp.CreateItem(olMailItem)

EmailItem.To = SendTo 'the email adress who the email is sent to

EmailItem.Subject = EmailSubject 'write your email subject in this variable

EmailItem.HTMLBody = EmailBody

Source = ThisWorkbook.FullName

'send the new email

EmailItem.Send

End Sub

To test the code, call this code using a buton:

Sub testSendEmail()
    Dim SendTo, EmailSubject, EmailBody As String
    SendTo = "myemail@example.com"   ' put the email adress that will receive the email
    EmailSubject = "This is a send email test from Excel"  ' write your subject
    EmailBody = "This email is sent from Execl using Microsoft outlook library" ' put the body
    sendAnEmail DomainName, SendTo, EmailSubject, EmailBody  ' call the subroutine by passing the parameters
End Sub

Sub testSendEmail()

Dim SendTo, EmailSubject, EmailBody As String

SendTo = "myemail@example.com" ' put the email adress that will receive the email

EmailSubject = "This is a send email test from Excel" ' write your subject

EmailBody = "This email is sent from Execl using Microsoft outlook library" ' put the body

sendAnEmail DomainName, SendTo, EmailSubject, EmailBody ' call the subroutine by passing the parameters

End Sub

Test: