CODEWITHSUNDEEP
emailvba
Lorenzo Rigamonti
Lorenzo Rigamonti

Reputation: 1775

Copying multiple lines in mail.Body in VB

I wanted to add multiple lines to the body of a mail using VB. Below the code for openining and formatting the mail:

With Workbooks("HEelo world.xlsx").Worksheets("Hello")
    mail.To = .Range(.Range("F:F").Find("Address1").Address).Offset(0, 1)
    mail.Cc = .Range(.Range("F:F").Find("Address2").Address).Offset(0, 1)
    mail.Subject = .Range(.Range("F:F").Find("Object").Address).Offset(0, 1)
    mail.Body = .Range(.Range("F:F").Find("Body").Address).Offset(0, 1)
    mail.Body = ....
End With

What is the correct syntax for adding multiple lines?

Upvotes: 1

Views: 579

Answers (2)

Tom 'Blue' Piddock
Tom 'Blue' Piddock

Reputation: 2210

Dummies Guide to naming Excel ranges

Use the link I've provided to learn how to modify a range to have a Name. You can target a range as a named reference in your vba. For this example we have called the cell range (eg.B4:B8) that contains the value for the mail body "BodyValue"

Dim strBody As String
    strBody = ""
    For Each cl in Range("BodyValue").Cells
        strBody = cl.Value & vbCrLf
    Next
mail.Body = strBody

You can do this for all parts used in your email as well so instead of using an Offset, name your cells or ranges and reference the names in your VBA. This will also avoid errors occuring if you add a row or column in any part of your sheet as named cells keep their name independant of changes to their address in the sheet.

Upvotes: 1

Lorenzo Rigamonti
Lorenzo Rigamonti

Reputation: 1775

I found this which should work: 

Dim strBody As String
        strBody = .Range(.Range("B:B").Find("text1").Address).Offset(0, 1) & vbCrLf _
        & .Range(.Range("B:B").Find("text2").Address).Offset(0, 1) & vbCrLf _
        & .Range(.Range("B:B").Find("text3").Address).Offset(0, 1) & vbCrLf _
        & .Range(.Range("B:B").Find("text4").Address).Offset(0, 1) & vbCrLf 
mail.Body = strBody

Upvotes: 1

Related Questions