Avatar billede Nuser2008 Mester
20. september 2020 - 15:13 Der er 2 kommentarer og
1 løsning

Opret tekst før og efter et billede er kopieret fra Excel til Outlook

Hej forum

Jeg har brug for jeres hjælp.

Jeg vil gerne i en e-mail have indføjet en tekst før og efter det billede, som jeg får kopieret ind fra min Excel-fil. Med nedenstående kode får jeg kopieret det ønskede range i Excel-filen og indsat det som billede i mailen. Men det er ikke lykkedes mig at få indarbejdet en tekst før og efter billedet. Kan I hjælpe?

På forhånd tak for jeres hjælp!

--------

Sub CreateMail

    Dim wb As Workbook
    Set wb = ThisWorkbook
   
    Dim ws1 As Worksheet
    Set ws1 = Sheets("All information")

    Dim ol As Object 'Outlook.Application
    Dim olEmail As Object 'Outlook.MailItem
    Dim olInsp As Object 'Outlook.Inspector
    Dim wd As Object 'Word.Document 

    Dim strbody As String
    strbody = "Hi,<br><br>Please take a look at this overview."
    strbody = strbody & picture 'This is the place for the picture
    strbody = strbody & "<br><br>Best Regards,<br>Arne<br><br>

    ws1.Range("B4:L5").SpecialCells(xlCellTypeVisible).Copy

    Set ol = GetObject(, "Outlook.Application")
    Set olEmail = ol.CreateItem(0)
   
    With olEmail
    .To = ws1.Range("D5")
    .Subject = "New procedure"
    .htmlbody = strbody
   
    Set olInsp = .GetInspector
    If olInsp.EditorType = 4 Then 'olEditorWord
        Set wd = olInsp.WordEditor
        wd.Range.PasteandFormat 13 'wdChartPicture
    End If
   
    For Each shp In wd.InlineShapes
        shp.ScaleHeight = 100
        shp.ScaleWidth = 100
    Next

    .Display

End With

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

End Sub
Avatar billede terry Ekspert
20. september 2020 - 16:19 #1
From your question it sounds as though you can insert a picture into the mail with the code you show, but the problem is adding text before and after the picture.

I'm rather surprised that you are able to get the picture into the mail, althoug I havent tested myself, but I wouldnt have thought that you can insert a picture into a string as you do here.

Dim strbody As String
    strbody = "Hi,<br><br>Please take a look at this overview."
    strbody = strbody & picture 'This is the place for the picture
    strbody = strbody & "<br><br>Best Regards,<br>Arne<br><br>


Anyway, maybe this youtube video can point you in the right direction

https://www.youtube.com/watch?v=aHOWhxDa7XI
Avatar billede Nuser2008 Mester
21. september 2020 - 08:21 #2
Hi Terry,

Thanks for your quick reply and good solution. It (of course) works. And - it's a great video you were referring to. I will take a look at the owls other videos.

Btw: The question/reflection regarding:
  strbody = strbody & picture 'This is the place for the picture

… I wanted to show, where I would like the picture to be placed after the first text section and before the next section. Sorry it wasn't well described.

Kind regards, Arne
Avatar billede terry Ekspert
21. september 2020 - 09:56 #3
Hi Arne
Great the video helped you on the way :-)

BR Terry
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester