+ Reply to Thread
Results 1 to 6 of 6

Cannot figure out how to add blank line between pasted ranges in Outlook email body

Hybrid View

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Unhappy Cannot figure out how to add blank line between pasted ranges in Outlook email body

    Greetings. I managed to piece this code together but no matter what I modify or how I modify it I cannot figure out how to simply insert a blank line between each range that I am pasting into the body. They appear as tables in the email body and I need one line between them. I would really appreciate help. I hope I posted in the right board. A solution that utilizes the inspector and word editor is preferred. =/

        Set OutApp = New Outlook.Application
        Set OutMail = OutApp.CreateItem(0)
    
        With OutMail
            '.To = ""
            .CC = ""
            .BCC = ""
            .Subject = ""
            .HTMLBody = "<HTML><body><body></HTML>"
            .Display
    
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
    
            oRng.InsertAfter vbCrLf & vbCrLf
    
            Set oRng = wdDoc.Range
            oRng.Collapse 0
    
            DoEvents
            rng_Wages_wbk_Wages_List.Copy
            DoEvents
            oRng.Paste
            DoEvents
            wbk_Wages.Application.CutCopyMode = False
    
            DoEvents
            wks_Employee_Charges.Range("X1").CurrentRegion.Copy
    '        rng_POHC_wbk_Employee_Payouts_And_House_Charges.Copy
            DoEvents
            oRng.Paste
            DoEvents        
            wbk_POHC.Application.CutCopyMode = False
            oRng.Move 1, -1
    
            wks_PayrollEntry.Activate
    
            DoEvents
            With Range(Cells(1, 3), Cells(27 - Blank_Payroll_Summary_Names_Count, 6))
                .Copy
            End With
            DoEvents
            oRng.Paste
            DoEvents
            
            wbk_Payroll.Application.CutCopyMode = False
            OutMail.Display
    
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
        Set oRng = Nothing
    Last edited by lOYvEpi6M87nEoIF0ul8; 07-22-2023 at 10:46 PM. Reason: Clarification

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,015

    Re: Cannot figure out how to add blank line between pasted ranges in Outlook email body

    With OutMail
        .To = "Your email address here in quotes"
        .CC = ""
        .BCC = ""
        .Subject = "Summary Data"
    
        .HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                    RangetoHTML(rng) & "<br><br>" & _
                    "Text below Excel cells.</p>"
            
        ' In place of the following statement, you can use ".Send" to
        ' Send the e-mail message.
        .Display
    End With
    Using the HTML code : & "<br><br>" & creates the empty line.

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Cannot figure out how to add blank line between pasted ranges in Outlook email body

    Thank you for your swift and helpful reply. I don't have the code for a function that converts ranges to html but looking it up I found one. The trouble I came across when I tried to use a similar solution that utilized temp files the range data was appearing in the email on my end with the desktop version of Outlook but not in the body of emails for IOS users specifically for some reason. In any case I would really like to stick with manipulating the word editor if I can.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,015

    Re: Cannot figure out how to add blank line between pasted ranges in Outlook email body

    Paste this below your macro for the email :

    ''<<<>>> There is no need to edit anything in this Function.
    
    
    Function RangetoHTML(rng As Range)
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
        
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        
        Set TempWB = Workbooks.Add(1)
        
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
        
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
        
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
        'Close TempWB
        TempWB.Close savechanges:=False
        
        'Delete the htm file we used in this function
        Kill TempFile
        
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
        
    End Function

  5. #5
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Cannot figure out how to add blank line between pasted ranges in Outlook email body

    Thank you.

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,015

    Re: Cannot figure out how to add blank line between pasted ranges in Outlook email body

    You are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need vba to copy body of outlook email ,paste into sheet email in workbook
    By breadwinner in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2022, 08:08 AM
  2. [SOLVED] Generating Outlook Email and inseting chart into email with Text body.
    By Hyperion1571 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2019, 12:41 PM
  3. vba pull data from outlook body of email through email or subject of mail into excel
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2014, 04:37 AM
  4. [SOLVED] Using Exell VBA to launch Outlook Email while separating lines in the body of Outlook.
    By Tazyote in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-27-2013, 01:18 PM
  5. export outlook 2007 email into excel with subject and body of email
    By akulka58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 02:37 PM
  6. Copy and Paste Union of Ranges/Rows to Outlook Email Body
    By darkhunter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2012, 01:52 PM
  7. Replies: 2
    Last Post: 08-01-2012, 02:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1