How to Send Email in Excel Using VBA?

Question

98

Anonymous

Asked: July 31, 20222022-07-31T14:45:30+00:00 2022-07-31T14:45:30+00:00Computer programming

How to Send Email in Excel Using VBA?

Hello,

I have an excel file that is updated automatically from some websites.

This files contains products prices. I want to send automatically an email when the price drop under a value. Is there a way How to send email using VBA Excel?How to Send Email in Excel Using VBA?

Thanks for your help

0

Be the first one react

3 Answers

Leave an answer

Leave an answer
Cancel reply

Anonymous · Answer 1 · 2022-07-31T15:22:27+00:00

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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

1

2

3

4

5

6

7

8

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:

I hope this will help you.

Good luck!

How to Send Email in Excel Using VBA?

How I sort worksheets by alphabet using vba Excel?

3 Answers

How to solve Data corruption prior to sending to F-IO ...

How to update SEO Focus keyphrase programmatically of woocommerce product?