# Have a gmail vba is it possible to add a range of cells into text body?



## tonywatsonhelp (Dec 29, 2017)

Hi Everyone,

I have this Vba that send an email via gmail works great,

however in the text body i'd like to add the data from "Sheet Data1 range B5:J15"
but make it look like the excel so include the grid 

So the message would look a bit like this

Hi,


And
this
would 
be 
the
data
in
the
range


<tbody>

</tbody>

can it be done and if so how?

please help

thanks

Tony


```
Sub send_email_via_gmail11()
Dim myMail As CDO.Message
Set myMail = New CDO.Message
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = True
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "me@gmail.com"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password1"
myMail.Configuration.Fields.Update
With myMail
.Subject = "Untimate Test2"
.From = "me@gmail.com"
.To = "you@gmail.com"
'.CC=
'.bcc=
.TextBody = "Hi"
End With
On Error Resume Next
myMail.Send
MsgBox ("Mail has been send")
Set myMail = Nothing
End Sub
```


----------



## lhartono (Dec 30, 2017)

https://www.rondebruin.nl/win/s1/outlook/bmail2.htm


----------



## tonywatsonhelp (Dec 30, 2017)

That's all about outlook i'm trying to use gmail?

any help would be appreciated

Tony


----------



## Logit (Dec 30, 2017)

.
*Untested BUT Ron is the "go to source" for everything email ...*



```
Option Explicit




'If you have a GMail account then you can try this example to use the GMail smtp server
'The example will send a small text message
'You must change four code lines before you can test the code


'.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
'.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"


'Use your own mail address to test the code in this line
'.To = "Mail address receiver"


'Change YourName to the From name you want to use
'.From = """YourName"" (Reply@something.nl)"


'If you get this error : The transport failed to connect to the server
'then try to change the SMTP port from 25 to 465


'Possible that you must also enable the "Less Secure" option for GMail
'https://www.google.com/settings/security/lesssecureapps


Sub CDO_Mail_Small_Text_3()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
    
    Dim r As Range          ''**************************************************
    Set r = Worksheets("Sheet1").Range("F1:F59").SpecialCells(xlCellTypeVisible)  '**************************************
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"


        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With


    strbody = "Hi there" & vbNewLine & vbNewLine & _
        "This is line 1" & vbNewLine & _
        "This is line 2" & vbNewLine & _
        "This is line 3" & vbNewLine & _
        "This is line 4"


    With iMsg
        Set .Configuration = iConf
        .To = "Mail address receiver"
        .CC = ""
        .BCC = ""
        ' Note: The reply address is not working if you use this Gmail example
        ' It will use your Gmail address automatic. But you can add this line
        ' to change the reply address  .ReplyTo = "Reply@something.nl"
        .From = """YourName"" (Reply@something.nl)"
        .Subject = "Important message"
        
        .HTMLBody = RangetoHTML(r) '****************************************************************************
        
        '.Addattachment "c:\temp\Scripty.zip"    ' --- edit path to file
        .Send
        
    End With
    
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub
'*************************************************************
'INCLUDE THE ENTIRE FUNCTION BELOW THE EMAIL MACRO THAT IS ABOVE
'*************************************************************
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    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


'INCLUDE THE FUNCTION ABOVE
'*******************************************************************************
```

*Note the workbook download with samples, about middle of the page :  https://www.rondebruin.nl/win/s1/cdo.htm*


----------



## lhartono (Dec 30, 2017)

Ya, you need Ron's - RangeToHTML function, and then change Textbody to HTMLBody.


```
[COLOR=#0000ff].HTMLBody = RangetoHTML(Sheets("Data1").Range("B5:J15"))[/COLOR]
```


----------



## tonywatsonhelp (Dec 30, 2017)

Thanks everyone,
Adding the RangetoHTML function then sett the .HTML body and it works like a dream, thank for all your help

Tony


----------



## Logit (Dec 30, 2017)

Glad you have the answer.

Cheers !


----------



## gagey (Jan 26, 2019)

*Re: Create Gmail Draft?*

Hello:  thank you for the solutions so far; I am now automating the creation of gmail emails, adding spreadsheet ranges to the email body and sending PDF attachments within excel using vba, however the one thing I cannot figure out, is how to create an email draft only, so that I can review and make changes necessary before sending the email. I've figured out how to do this with Outlook, but not with Gmail...

Any help would be greatly appreciated!

Gagey.


----------



## daverunt (Jan 28, 2019)

*Re: Create Gmail Draft?*

Hi,

you should start a new thread for this. It's a new post and I may be proved wrong but I cannot see how it is possible.

You are using the SMTP service on your PC which is a mail transport protocol to send your mail to Gmail servers.
There is no application to 'see the mail' you merely send the information in the correct format.

Sending via Outlook allows you that flexibility to view generated mails etc.


----------



## gagey (Jan 28, 2019)

*Re: Create Gmail Draft?*

Hi:  I realized yesterday that I probably need to post this as a new thread (which I  later did).  Thank you so much for your reply; good to know that this can be done with Outlook but not Gmail.

Have a great day!

Gagey.


----------

