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

 

[ziad alsayed]

dear jeff

thanks for the reply, how do you suggest in the code?

 

[daverunt]

Reposting

 

[daverunt]

Hi

You are using HTML for the email body so any vbCrLfs added to the msg are ignored.
You can get around this by adding HTML to the msg as it's constructed, i.e table row tags or break tags.
But as you are displaying the msg in a MsgBox then any HTML tags will appear in the message box as text.

To get around this add a break tag to the msg then strip it out for the message box:

Also Google RangeToHTML - I have never used this but it may be of help.

If Cells(i, 2).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Today" & vbCrLf & vbCrLf & "<br/>"
            If Cells(i, 3).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Tomorrow" & vbCrLf & vbCrLf & "<br/>"
                If Cells(i, 4).Value = Date Then Msg = Msg & Cells(i, 1).Value & "'s" & " Birthday Is Next Tomorrow" & vbCrLf & vbCrLf & "<br/>"
    Next i
        Output = Replace(Msg, "<br/>", "")
        If Msg <> "" Then MsgBox Output, , "Ziad Alsayed"