Hoppa till huvudinnehåll

Hur skickar jag ett specifikt diagram i ett e-postmeddelande med vba i Excel?

Du kanske vet hur du skickar ett e-postmeddelande via Outlook i Excel med VBA-kod. Men vet du hur du bifogar ett visst diagram i ett visst kalkylblad i e-postens kropp? Denna artikel kommer att visa dig metoden för att lösa detta problem.

Skicka ett specifikt diagram i ett e-postmeddelande i Excel med VBA-kod

Skicka ett specifikt diagram i ett e-postmeddelande i Excel med VBA-kod

Gör så här för att skicka ett specifikt diagram i ett e-postmeddelande med VBA-kod i Excel.

1. I kalkylbladet innehåller diagrammet du vill bifoga i e-posttexten, tryck på andra + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. I Microsoft Visual Basic för applikationer klicka på fönstret Insert > Modulerna. Kopiera sedan VBA-koden nedan till kodfönstret.

VBA-kod: Skicka ett specifikt diagram i ett e-postmeddelande i Excel

Sub mailHTMLsend()
'Updated by Extendoffice 2018/3/5
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xStartMsg As String
    Dim xEndMsg As String
    Dim xChartName As String
    Dim xChartPath As String
    Dim xPath As String
    Dim xChart As ChartObject
    On Error Resume Next
    xChartName = Application.InputBox("Please enter the chart name:", "KuTools for Excel", , , , , , 2)
    If xChartName = "" Then Exit Sub
    Set xChart = Sheets("Sheet1").ChartObjects(xChartName) 'Change "Sheet1" to your worksheet name
    If xChart Is Nothing Then Exit Sub
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xStartMsg = "<font size='5' color='black'> Good Day," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
    xEndMsg = "<font size='4' color='black'> Many Thanks," & "<br> <br> </font>"
    xChartPath = Application.ActiveWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    xPath = "<p align='Left'><img src=" & "cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """  width=700 height=500 > <br> <br>"
    xChart.Chart.Export xChartPath
    With xOutMail
        .To = ""
        .Subject = "Add Chart in outlook mail body"
        .Attachments.Add xChartPath
        .HTMLBody = xStartMsg & xPath & xEndMsg
        .Display
    End With
    Kill xChartPath
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Anmärkningar: Ändra mottagarens e-postadress och e-postens ämne i koden i koden .Till = "" och linje .Subject = "Lägg till diagram i Outlook-e-posttexten" , Sheet1 är arket som innehåller diagrammet du vill skicka, ändra det till ditt eget.

3. tryck på F5 för att köra koden. I öppningen Kutools för Excel dialogrutan, ange namnet på diagrammet som du ska bifoga i e-posttexten och klicka sedan på OK knapp. Se skärmdump:

Sedan skapas ett e-postmeddelande automatiskt med det angivna diagrammet som visas i e-postkroppen som visas nedan. Klicka på knappen Skicka för att skicka detta e-postmeddelande.

Relaterade artiklar:

 

 

 

Bästa kontorsproduktivitetsverktyg

🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup  |   VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar   |  Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler   |  Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser:  12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50+ Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

Beskrivning

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!

 

 

Comments (15)
No ratings yet. Be the first to rate!
Jyoti
Jyoti
This comment was minimized by the moderator on the site
With the above code, Chart can be seen in the Email body with the .Display.

When .Send is used to send the message, Mail dosent show chart in the Email body.
Reply
0
0
crystal
crystal     Jyoti
This comment was minimized by the moderator on the site
Hi Jyoti,

Can you tell me what version of Excel you are using? The code works fine whether I change .Display to .Send or not.
Reply
Report
0
0
pavan chougule
pavan chougule
This comment was minimized by the moderator on the site
HELLO , I want to add space in mail body , which keyword should I use.
Reply
0
0
crystal
crystal     pavan chougule
This comment was minimized by the moderator on the site
Hi pavan chougule,
The following two lines in the code contain the email body content. You can manually modify the email body by pressing the space key on your keyboard to add a space.
xStartMsg = "<font size='5' color='black'> Good Day," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
xEndMsg = "<font size='4' color='black'> Many Thanks," & "<br> <br> </font>"
Reply
Report
0
0
YOussef
YOussef
This comment was minimized by the moderator on the site
It includes the chart as an attachment. Do you have any idea how to include it as a picture in the mail body itself. Thank you,Youssef
Reply
0
0
J
J     YOussef
This comment was minimized by the moderator on the site
Same problem, any solution?
Reply
0
0
crystal
crystal     J
This comment was minimized by the moderator on the site
Hi J，
The code has been updated. Please give it a try. Sorry for the inconvenience.


Sub mailHTMLsend()
'Updated by Extendoffice 2018/3/5
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xStartMsg As String
    Dim xEndMsg As String
    Dim xChartName As String
    Dim xChartPath As String
    Dim xPath As String
    Dim xChart As ChartObject
    On Error Resume Next
    xChartName = Application.InputBox("Please enter the chart name:", "KuTools for Excel", , , , , , 2)
    If xChartName = "" Then Exit Sub
    Set xChart = Sheets("Sheet1").ChartObjects(xChartName) 'Change "Sheet1" to your worksheet name
    If xChart Is Nothing Then Exit Sub
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xStartMsg = "<font size='5' color='black'> Good Day," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
    xEndMsg = "<font size='4' color='black'> Many Thanks," & "<br> <br> </font>"
    xChartPath = Application.ActiveWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    xPath = "<p align='Left'><img src="/%20&%20"cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """  width=700 height=500 > <br> <br>"
    xChart.Chart.Export xChartPath
    With xOutMail
        .To = ""
        .Subject = "Add Chart in outlook mail body"
        .Attachments.Add xChartPath
        .HTMLBody = xStartMsg & xPath & xEndMsg
        .Display
    End With
    Kill xChartPath
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
Reply
Report
0
0
Kuba
Kuba     crystal
This comment was minimized by the moderator on the site
Cześć,
mi nic sie nie załącza, czy coś tutaj należałoby wpisać jeszcze?
xPath = "co tutaj trzeba wprowadzić?"
Reply
0
0
crystal
crystal     Kuba
This comment was minimized by the moderator on the site
Hi Kuba,
The code has been updated. The recipient can view the chart normally. Please give it a try.
Note: In the code, please change the "Chart 1" to your own chart name. And specify the email address in the To field.
Sub mailHTMLsend()
'Updated by Extendoffice 20221013
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xStartMsg As String
    Dim xEndMsg As String
    Dim xChartName 'As String
    Dim xChartPath As String
    Dim xPath As String
    Dim xChart As ChartObject
    On Error Resume Next
    xChartName = "Chart 1" 'The name of the chart in the current worksheet you want to send.
    If xChartName = "" Then Exit Sub
    Set xChart = Application.ActiveSheet.ChartObjects(xChartName)
    If xChart Is Nothing Then Exit Sub
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    xStartMsg = "<font size='5' color='black'> Good Day," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
    xEndMsg = "<font size='4' color='black'> Many Thanks," & "<br> <br> </font>"
    xChartPath = Application.ActiveWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    
    xPath = "<p align='Left'><img src="/%20&%20"cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """  width=700 height=500 > <br> <br>"
    
    xChart.Chart.Export xChartPath
    With xOutMail
        .To = "Email Address"
        .Subject = "Add Chart in outlook mail body"
        .Attachments.Add xChartPath
        .HTMLBody = xStartMsg & xPath & xEndMsg
        .Display
    End With
    Kill xChartPath
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
Reply
Report
0
0
crystal
crystal     Kuba
This comment was minimized by the moderator on the site
Hi Kuba,
Please remove the / tag in <img src="/.
The error is caused by the editor in the site.
Sorry for the inconvenience.
Reply
Report
0
0
Kuba
Kuba     crystal
This comment was minimized by the moderator on the site
cześć, pełny kod działa tylko do momentu podglądu komunikatu, przy wysyłce adresat otrzymuje błąd i wykresu nie widać ("Nie można wyświetlić połączonego obrazu. Plik mógł zostać przeniesiony lub usunięty albo zmieniono jego nazwę. Sprawdź czy łącze wskazuje poprawny plik i lokazlizację.") Czy z Was też tak ktoś miał czy tylko u mnie taki zonk? Prosze o pomoc, tutaj kod, który dotyczy wykresum już tak mało brakuje :)

Dim xChartName As String
Dim xChartPath As String
Dim xPath As String
Dim xChart As ChartObject
On Error Resume Next
Dim wydzialy As String
wydzialy = lista.Cells(3, 75)
xChartName = Application.InputBox(wydzialy, "KuTools for Excel", , , , , , 2) 'Wykres1 '"Please enter the chart name:"
If xChartName = "" Then Exit Sub
Set xChart = Sheets("Wykresy").ChartObjects(xChartName) 'Change "Sheet1" to your worksheet name
If xChart Is Nothing Then Exit Sub
xChartPath = Application.ActiveWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".svg" '.bmp '.svg '.svg ma lepsza jakość
xPath = "<p align='Left'><img src="/%20&%20"cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """ width=500 height=300 > <br> <br>"
xChart.Chart.Export xChartPath


Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = emails(b)
.CC = emails_dw(b)
.Subject = "XXXX" ' - " & lista.Cells(i, 66)
.Attachments.Add xChartPath
.HTMLBody = "treść" & xPath

Set .SendUsingAccount = OutApp.Session.Accounts.Item(1)

.Display
End With
Kill xChartPath
Set OutMail = Nothing
Set OutApp = Nothing
0
0
Nitin Sharma
Nitin Sharma
This comment was minimized by the moderator on the site
There is error in the code : "\") + 1) & """ width=700 height=50In the bold text the middle one should be a single inverted comma

Reply
0
0
Aaron Manqueo
Aaron Manqueo
This comment was minimized by the moderator on the site
hola como puede enviar por correo, una tabla dinámica, y no un gráfico
Reply
0
0
leigh
leigh
This comment was minimized by the moderator on the site
when i enter the chart name the mail doesn't generate the dialog box just closes, any idea what i have done wrong? I have followed each step
Reply
0
0
Borges
Borges     leigh
This comment was minimized by the moderator on the site
The issue is that we can not set names for Chart Objects like tables. You need to pass the integer ID to work. For instance, if you only have 1 chart in the "Sheet1", trying passing the value 1 when the msgbox shows up.

PS: sorry for the bad english :]
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations