VBA Send e-mail fra Excel | Trin for trin kode for at sende e-mail

VBA-kode til at sende e-mails fra Excel

I VBA for at sende e-mail fra Excel kan vi automatisere vores mailing-funktion, så vi automatisk kan sende e-mails til flere brugere ad gangen. For at gøre det skal vi huske, at det gøres ved at Outlook et andet Outlook-produkt, så vi skal aktivere Outlook scripting i VBA for at gøre det, og når det er gjort, bruger vi .Application-metoden til at bruge Outlook-funktioner.

VBA's alsidighed er bare fantastisk. Årsagen til, at VBA-kodere elsker excel, fordi vi ved at bruge VBA ikke kun kan arbejde inden for excel, men vi kan også få adgang til andre Microsoft-værktøjer. Vi kan få adgang til PowerPoint, Word og Outlook ved hjælp af VBA. Det, der imponerede mig, var, da jeg hørte om "at sende e-mail fra excel selv". Ja, det er sandt, at vi kan sende e-mails fra excel. I denne artikel viser vi dig, hvordan du sender e-mails fra excel med vedhæftede filer ved hjælp af VBA-kodning.

Indstil reference til Microsoft Office-bibliotek

Vi skal sende e-mails fra Outlook. Da Outlook er et eksternt objekt, er det første, vi skal gøre, at sætte objektreferencen til " Microsoft Outlook 16.0 Object Library ".

  • Gå til Værktøjer> Referencer i VBA

  • Nu vil vi se objektreferencebiblioteket. I dette vindue skal vi indstille henvisningen til "Microsoft Outlook 16.0 Object Library"

  • Efter indstilling af objektreferencen skal du klikke på Ok.

Nu kan vi få adgang til Outlook-objekt i VBA-kodning.

13 nemme trin til at sende e-mails fra Excel

At skrive koden for at sende en e-mail med en vedhæftet fil fra Excel er ret kompliceret, men det er værd at bruge lidt tid.

Du kan downloade denne VBA Send e-mail-Excel-skabelon her - VBA Send e-mail-Excel-skabelon

Følg nedenstående trin for at skrive din første Excel Excel-makro.

Trin 1

Start delproceduren i VBA.

Kode:

 Sub SendEmail_Example1 () Afslut Sub 

Trin 2

Erklær variablen Outlook.Application

Kode:

 Dim EmailApp As Outlook.Application 'For at henvise til Outlook-applikationen 

Trin # 3

Ovenstående variabel er en objektvariabel. Vi er nødt til at oprette en forekomst af et nyt objekt separat. At oprette en ny forekomst af det udvendige objekt nedenfor er koden.

Kode:

 Indstil EmailApp = New Outlook.Application 'For at starte Outlook-applikationen 

Trin # 4

For at skrive e-mailen erklærer vi endnu en variabel som “Outlook.MailItem”.

Kode:

 Dim EmailItem As Outlook.MailItem 'For at henvise ny Outlook-e-mail 

Trin # 5

For at starte en ny e-mail er vi nødt til at indstille henvisningen til vores tidligere variabel som CreateItem.

Kode:

 Indstil EmailItem = EmailApp.CreateItem (olMailItem) 'For at starte ny Outlook-e-mail 

Ok, nu starter variablen "EmailApp" outlook, og i variablen "EmailItem" kan vi begynde at skrive e-mailen.

Trin # 6

Now we need to be aware of what are the items we have while writing an email. The first thing is we need to decide for whom we are sending the email. So for this, we need to access “TO” property.

Step #7

Enter the email Id of the receiver in double-quotes.

Code:

EmailItem.To = "[email protected]"

Step #8

After addressing to the main receiver, if you would like to CC anyone in the email, we can use the “CC” property.

Code:

EmailItem.CC = "[email protected]"

Step #9

After the CC we can set BCC email id as well.

Code:

EmailItem.BCC = "[email protected]"

Step #10

Now we need to include Subject of the email we are sending.

Code:

EmailItem.Subject = "Test Email From Excel VBA"

Step #11

Now we need to write the body of the email by using HTML Body type.

Code:

EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _ vbNewLine & vbNewLine & _ "Regards," & vbNewLine & _ "VBA Coder" 'VbNewLine is the VBA Constant to insert a new line 

Step #12

If we want to add an attachment as the current workbook we are working on then we need to use attachments property. First, declare a variable source as a string.

Code:

 Dim Source As String 

Then in this variable write ThisWorkbook.FullName after Email body.

Code:

Source = ThisWorkbook.FullName

In this VBA Code, ThisWorkbook is used for the current workbook and .FullName is used to get the full name of the worksheet.

Then write the following code to attach the file.

Code:

EmailItem.Attachments.Add Source

Step #13

Now finally we need to send the email to the mentioned email ID’s. This can be done by using “Send” method.

Code:

EmailItem.Send

Ok, we are done with the coding part.

Code:

 Sub SendEmail_Example1() Dim EmailApp As Outlook.Application Dim Source As String Set EmailApp = New Outlook.Application Dim EmailItem As Outlook.MailItem Set EmailItem = EmailApp.CreateItem(olMailItem) EmailItem.To = "[email protected]" EmailItem.CC = "[email protected]" EmailItem.BCC = "[email protected]" EmailItem.Subject = "Test Email From Excel VBA" EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _ vbNewLine & vbNewLine & _ "Regards," & vbNewLine & _ "VBA Coder" Source = ThisWorkbook.FullName EmailItem.Attachments.Add Source EmailItem.Send End Sub 

Run the above code and it will send the email with the mentioned body of the email with the current workbook as the attachment.