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

Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more