Macro to insert text from cells in body in email

[KlausW]

Hi

Someone who can help, I use this VBA Code to send mail. It works well. Now I would like to be able to write mails body in some cells range I3, I9 and I11, so I can change it from time to time.

All help will be appreciated.
Best regards Klaus

 

[Crystalyzer]

Can you post the code you are using to send the email?

 

[KlausW]

Sorry I did not get the code above, but here it is. KW

Sub Mail_workbook_Outlook()
'her er koden til at sende mail
    Dim Edress As String, Subj As String
    Dim OutlookOBJ As Object, mItem As Object
    '---------------------------------------------'
    Set OutlookOBJ = CreateObject("Outlook.Application")
    Set mItem = OutlookOBJ.CreateItem(olMailItem)
    With mItem
    
        .To = Sheets("Bestilling").Range("i1").Value
        .CC = ""
        .BCC = ""
        .Subject = Sheets("Bestilling").Range("a1").Value
       
        .Body = "Hej Bo" & vbNewLine & _
                " " & vbNewLine & _
              "Vil du bestille dette for mig." & vbNewLine & _
                " " & vbNewLine & _
                "Med venlig hilsen" & vbNewLine & _
               " " & vbNewLine & _
                "Klaus Wittusen" & vbCrLf & "Oversergent" & vbCrLf & "Proviantbefalingsmand" & vbCrLf & "Kongeskibet Dannebrog"
        
        '.Send                               '<-- .Send will auto send email without review
                                
 ThisWorkbook.Save
        .Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
        .Send
    End With

End Sub

 

[Crystalyzer]

Simply replace the text with the cell values you want as in the code below.

.Body = Range(I3).Value & vbNewLine & _
                " " & vbNewLine & _
              Range(I9).Value & vbNewLine & _
                " " & vbNewLine & _
                Range(I11).Value & vbNewLine & _
               " " & vbNewLine & _
                "Klaus Wittusen" & vbCrLf & "Oversergent" & vbCrLf & "Proviantbefalingsmand" & vbCrLf & "Kongeskibet Dannebrog"

 

[KlausW]

hi again i changed code to. .Body = Range ("I3") .Value then Excel takes the text from cell I3. but I can not get it to take the other cells. KW

 

[Crystalyzer]

You may need to create the "body" in a string variable before setting it in the body

something like

Dim sBody as string

sBody = Range("I3").Value & vbNewLine & _
                " " & vbNewLine & _
              Range("I9").Value & vbNewLine & _
                " " & vbNewLine & _
                Range("I11").Value & vbNewLine & _
               " " & vbNewLine & _
                "Klaus Wittusen" & vbCrLf & "Oversergent" & vbCrLf & "Proviantbefalingsmand" & vbCrLf & "Kongeskibet Dannebrog"

then set the body using

.Body = sBody

 

[KlausW]

Good evening the previous solution seemed 110% thanks of the help, have a nice day KW