excel vba to put an excel range into body of gmail as plain text -

i have found vba code send email through gmail works cannot figure out how worksheets("sheet1").range("f1:f59") either .textbody or .htmlbody.

at first thought because had 2 ranges not sure

my latest attempt copy in excel don't know how paste plain text gmail (which i'm finding different outlook)

sub cdo_mail_small_text_2()  dim imsg object dim iconf object dim strbody string dim flds variant  '    dim rng range dim cell range   '    application  '        .screenupdating = false  '        .enableevents = false  '    end  set imsg = createobject("cdo.message") set iconf = createobject("cdo.configuration")  iconf.load -1    ' cdo source defaults set flds = iconf.fields 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") = "youremail"     .item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"     .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") = 465     .update end    '   set rng =  sheets("sheet1").range("f1:f59").specialcells(xlcelltypevisible)  '   set rng = activesheet.usedrange  '   set rng = sheets("sheet1").usedrange   '    set rng = nothing  '    on error resume next   '   set rng = selection.specialcells(xlcelltypevisible)   '   on error goto 0   '   if rng nothing  '       msgbox "the selection not range or sheet protected" & _  '              vbnewline & "please correct , try again.", vbokonly  '       exit sub  '   end if    '    strbody = "hi there" & vbnewline & vbnewline & _  '              "this line 1" & vbnewline & _  '              "this line 2" & vbnewline & _  '              "this line 3" & vbnewline & _  '              "this line 4"    '    worksheets("sheet1").range("f1:f59").copy  each cell in       sheets("sheet1").columns("b").cells.specialcells(xlcelltypeconstants)     if cell.offset(0, 1).value <> ""         if cell.value "?*@?*.?*" , lcase(cell.offset(0, 1).value) = "yes"             set imsg = createobject("cdo.message") imsg     set .configuration = iconf  '        .to = "mail address receiver"     .to = cell.value     .cc = ""     .bcc = ""     ' note: reply address not working if use gmail example     ' use gmail address automatic. can add line     ' change reply address  .replyto = "reply@something.nl"     .from = ""name""" <email>"     .subject = "changesubject"     .textbody = strboody  '        .htmlbody = (rng)     .send end             set imsg = nothing         end if     end if next cell   '    application  '        .enableevents = false  '        .screenupdating = false  '    end  end sub 

use this.

and suggest vist page comes @ ron de bruin

dim r range      set r = worksheets("sheet1").range("f1:f59").specialcells(xlcelltypevisible) 

then in part of code doing setup try this.

 .htmlbody = rangetohtml(r) 

this function.

function rangetohtml(rng range) ' changed ron de bruin 28-oct-2006 ' working in office 2000-2016     dim fso object     dim ts object     dim tempfile string     dim tempwb workbook      tempfile = environ$("temp") & "\" & format(now, "dd-mm-yy h-mm-ss") & ".htm"      'copy range , create new workbook past data in     rng.copy     set tempwb = workbooks.add(1)     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      'publish sheet htm file     tempwb.publishobjects.add( _          sourcetype:=xlsourcerange, _          filename:=tempfile, _          sheet:=tempwb.sheets(1).name, _          source:=tempwb.sheets(1).usedrange.address, _          htmltype:=xlhtmlstatic)         .publish (true)     end      'read data htm file 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 htm file used in function     kill tempfile      set ts = nothing     set fso = nothing     set tempwb = nothing end function