• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to populate email body in next line.

ThrottleWorks

ThrottleWorks

Excel Ninja
How to populate email body in next line.
For example if I want to populate mail as below

Text line 1
Blank line
Text line 2
Blank line
Text line3
Blank line
Text line 4
Blank line 4

I tried below mentioned code but it is not working.
I pass blank cells to create blank line even that is not working.

For example, E1 E2 and E5 are blank cells.

How can we do it. Can anyone please help me in this.

Code: 
Sub Email_Body_02()
 
    Dim strBody1 As String
    Dim strBody2 As String
    Dim strBody3 As String
    Dim strBody4 As String
 
    strBody1 = "<font size=""2"" face=""Calibri"" color=""Black"">" & EmailSht.Range("D1").Value & EmailSht.Range("E1").Value
 
    strBody2 = "<font size=""2"" face=""Calibri"" color=""Black"">" & EmailSht.Range("D2").Value & EmailSht.Range("E2").Value
 
    strBody3 = "<font size=""2"" face=""Calibri"" color=""Black"">" & EmailSht.Range("D5").Value & EmailSht.Range("E5").Value
 
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
        With OutMail
            .SentOnBehalfOfName = EmailSht.Range("b1")
            .To = EmailSht.Range("b2")
            .CC = EmailSht.Range("b3")
            .Subject = MacroSht.Range("z4")
            .HTMLBody = Msg & strBody1 & Chr(13) & vbNewLine & strBody2 & Chr(13) & vbNewLine & strBody3 & Chr(13)
            .Save
        End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Last edited:
Is something like this.

Code: 
Dim body_txt As String

With Sheets("Sheet1")
      body_txt = "<font size=2 face=Calibri color=Black>" & .[d1] & "<br>" & .[d2] & "<br>" & .[d5] & "<br>"
End With
   
        With CreateObject("Outlook.Application").CreateItem(0)
            .SentOnBehalfOfName = ""
            .To = ""
            .CC = ""
            .Subject = ""
            .HTMLBody = body_txt
            .display
        End With
 
Hi @Deepak Sir, could you please help on the below if you get time.

With Sheets("Sheet1")
body_txt = "<font size=2 face=Calibri color=Black>" & .[d1] & "<br>" & .[d2] & "<br>" & .[d5] & "<br>"
End With

I need to replace [d1] with a range. Is it possible to replace this reference with range. I tried editing this line but not successful.

I am trying to pass below mentioned range in place of [d1].

Set TempRng = Sht.Range(Sht.Cells(2, 1), Sht.Cells(TempLr, 1))
 
ThrottleWorks said:
I am trying to pass below mentioned range in place of [d1].

Set TempRng = Sht.Range(Sht.Cells(2, 1), Sht.Cells(TempLr, 1))
Click to expand...

What it's having & Where do you want to place it.

Can we have the sample xl with the dummy data & a snap of outlook email after manual preparation of your goal.
 
Hi @Deepak Sir, please find attached sample file for your reference. Thanks for the help. :)

Please note, data might be present in all the 4 regions are any of the 4 regions.
It is also possible that data will not be present in any of the region.

Also, as described in sample file, there is a bit of formatting involved in 'Region' name. This got me stuck.
 

Attachments

Last edited:
How about to simply create a template( with all range - rang1,range2.....) in xl & then use RangetoHTML to show it on outlook.

This will look something like as below:
upload_2015-10-28_17-58-8.png


what i used for this.

Code: 
Sub test1()
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = ""
        .CC = ""
      ' .BCC = ""
        .Subject = "'"
'        .BodyFormat = .olFormatHTML
        .HTMLBody = RangetoHTML(Sheet1.Range("D5:d43"))
        .Display
        '.Send
    End With

End Sub
 
Hi @Deepak Sir, thanks a lot for the help. I was trying the same thing. But problem here is I do not know how to format 'Region' from this range.

For example, in range D5:D43, D5 is region 1, D10 is region 2, D15 is region 3 and D20 is D25.

Now how do I format D5 ,D10 , D15 and D20 as bold and underline.
I am not able to achieve this result with current coding.

Good night. :)
 
Hi @Deepak Sir, you are :awesome:. Do not know what I was trying.

I was stuck about how to apply format. And while trying this I did not apply the format even manually. Should have done this, would have save your time and my headache also.

Good night. :)

:(:p:eek::oops:o_O
 
You must log in or register to reply here.
Back
Top