CODEWITHSUNDEEP
excelvbaexcel-2016
Lupus
Lupus

Reputation: 45

Another way to copy an Excel cell range in email body?

I have an issue while copying a range from Excel to email body. I already have a pretty much working script.

My issue is that the tool is crashing due to building too many temp_documents. I had the idea of coping the range directly in the email body, that also didn't work.

I need a better way to copy the data in the email_body. Can you please give me some other way/ways to copy an excel Range in mail_body?

enter image description here

This is the format of the data. The function that I use is RangeToHTML(rngHtmlBody)

Private 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"
    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 GoTo 0
    End With
    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
    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=")
    TempWB.Close savechanges:=False
    Kill TempFile
    Set ts = Nothing
    Set Fso = Nothing
    Set TempWB = Nothing
End Function

Upvotes: 0

Views: 57

Answers (1)

Cookie Monster
Cookie Monster

Reputation: 19

You can use a small email-sending freeware like BLAT through VBA. You can tell BLAT to use an HTML file you generate through your VBA as your email body, and then send the email. Never had an issue with that tool !

Open "c:\Temp\MailHeader.txt" For Output As #2
Print #2, Me.Subject
Close #2

Open "c:\Temp\MailBody.txt" For Output As #2
Print #2, Me.BodyText
Close #2

sBlatCommandLine = BLAT_PATH & " -to """ & sListOfEmails & """  -f ""noreply@mydomain.com""  -server webappserv.mydomain.com -html  -sf c:\Temp\MailHeader.txt  -bodyF c:\Temp\MailBody.txt "

Shell sBlatCommandLine

Upvotes: 0

Related Questions