VBA Email Body

[ziad alsayed]

dear all

the below code is working perfectly, simply it is will the date and will give a message Box infoming about the person or persons Birhtday and finally send an email.
the problem i am facing is that the code is pasting the content of the Message Box as One Line , i want to be broken as per the MessageBox.

Example: insteat of stating " Today is ziad birthday, Tomorrow is Allen Birthday, Today is John Birthday"

i want to show in the Email as

Today is ziad birthday,

Tomorrow is Allen Birthday,

Today is John Birthday.


below is the code

Sub Birthday()
Dim finalRow As Long
Dim i As Long
Dim Msg As String
Dim omail As Object
Dim oapp As Object
Dim SigString As String
Dim Signature As String
    Msg = ""
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To finalRow
        If Cells(i, 2).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Today" & vbCrLf & vbCrLf
            If Cells(i, 3).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Tomorrow" & vbCrLf & vbCrLf
                If Cells(i, 4).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Next Tomorrow" & vbCrLf & vbCrLf
    Next i
        If Msg <> "" Then MsgBox Msg, , "Ziad Alsayed"
    Set oapp = CreateObject("outlook.application")
    Set omail = oapp.createitem(0)
' add the email Signature
    SigString = "c:\Users\Ziad Alsayed\AppData\Roaming\Microsoft\Signatures\Ziad.htm"
                If Dir(SigString) <> "" Then
                    Signature = GetBoiler(SigString)
                Else
                    Signature = ""
                End If
' start preparing to send the email
        With omail
            .To = "[EMAIL="Ziad.alsayed@jubailibros.com"]Ziad.alsayed@jubailibros.com[/EMAIL]"
            .Subject = " BirthDay E-Mail"
            Msg = Msg
            Msg = Msg & "<br><br>"
            .htmlbody = Msg & "<br><br>" & Signature
            .send
        End With
        
    Set omail = Nothing
    Set oapp = Nothing
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
End Sub
Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.getfile(sFile).OpenAstextstream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

appreciate any Help

 

[bensonsearch]

In the msg part of code use

vbcrLf

eg: Msg = Msg & "" & vbcrLf


this will move down a line.

 

[ziad alsayed]

thanks Benson, but still the same .

 

[bensonsearch]

Sorry try putting it after the first Msg. you have two in a row.

Msg = Msg & vbcrLf
Msg = Msg & ""

 

[RichardMGreen]

Bit of a long shot, but try replacing each vbcrlf with chr(10).

 

[ziad alsayed]

thanks richard, but still didn't work.

though when the message box appears , it appears in 3 lines.

 

[RichardMGreen]

Copy the contents of a sent mail into notepad and see if there are any weird characters where the line breaks should be.

It's vaguely possible it's because you're using htmlbody.

 

[bensonsearch]

The only other thing is maybe it doesn't like sending in parts. Maybe make a new string variable that has the whole msg built in with vbcrlf in it and see if that works.

All VBA emails I use a single variable in the body.

Definaty need vbcrlf in there.

Oh as a thought. Can you use HTML code in there? Coz you could incorporate <br/> into the code

 

[bensonsearch]

The only other thing is maybe it doesn't like sending in parts. Maybe make a new string variable that has the whole msg built in with vbcrlf in it and see if that works.

All VBA emails I use a single variable in the body.

Definaty need vbcrlf in there.

Oh as a thought. Can you use HTML code in there? Coz you could incorporate
into the code

sorry didnt show. can incorporate a br into the vba code

 

[repairman615]

Hello,

Here is an example that may help:

<font face=Courier New>MsgBox "Top line" & vbNewLine & "Middle line" & vbNewLine & "Bottom line"</FONT>

Does that help?

-Jeff