excel vba - How do I add code to my Mail_Workbook vba to open everyday, refresh, send, then close? -


i need add vba open workbook, refresh data, automatically, send, close.

here code works fine on it's own need automate daily.

sub mail_workbook() dim outapp object dim outmail object dim emailaddr string dim subj string    set outapp = createobject("outlook.application") set outmail = outapp.createitem(0)  outmail .to = "me.meeee@company.com" .cc = "" .bcc = "" .subject = "***test*** " & subj .body = subj .attachments.add activeworkbook.fullname .display application.wait (now + timevalue("0:00:02")) application.sendkeys "%s" end set outmail = nothing end sub

you may try below. on workbook open calls procedure runmacro.

the runmacro procedure reads values ranges , sets time when mis procedure has called.

mis procedure open workbook, refresh , path save file , send mail.

in mail send link workbook , wont attach workbook. can save workbook on shared drive.

put code on thisworkbook code section

 private sub workbook_open()     runmacro end sub


put code in standard module.

sub runmacro()       dim string, b string, c string, d string, e string      = format(range("a3"), "hh:mm:ss")     b = format(range("a4"), "hh:mm:ss")     c = format(range("a5"), "hh:mm:ss")     d = format(range("a6"), "hh:mm:ss")     e = format(range("a7"), "hh:mm:ss")       application.ontime timevalue(a), "mis"     application.ontime timevalue(b), "mis"     application.ontime timevalue(c), "mis"     application.ontime timevalue(d), "mis"     application.ontime timevalue(e), "mis" end sub  sub mis()  'open workbook     dim wkb workbook     dim path string, strfile string, strfilepath string      strfile = "file1.xlsx"     path = thisworkbook.path & "\" & strfile      if isworkbookopen(path)         set wkb = workbooks(strfile)     else         set wkb = workbooks.open(path)     end if      'refresh data     wkb.refreshall      'get new filepath     strfilepath = getfilelink      wkb.saveas filename:=strfilepath      wkb.close      'send mail     sendmail strfilepath   end sub  function isworkbookopen(filename string) 'check if workbooks open 'isopen return true      dim ff long, errno long      on error resume next     ff = freefile()     open filename input lock read #ff     close ff     errno = err     on error goto 0      select case errno     case 0: isworkbookopen = false     case 70: isworkbookopen = true     case else: error errno     end select end function  sub sendmail(mydest string) 'procedure send mail 'you need configure server & port      dim imsg object     dim iconf object     dim flds variant       set imsg = createobject("cdo.message")     set iconf = createobject("cdo.configuration")      iconf.load -1     set flds = iconf.fields      flds         .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2         .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "test-svr-002"         .item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25         .update     end      imsg          set .configuration = iconf         .to = "test@gmail.com"         .from = "test@gmail.com"         .subject = "mis reports" & " " & date & " " & time         .textbody = "link mis report :" & vbnewline & "<" & mydest & ">"         .send     end      set imsg = nothing     set iconf = nothing  end sub  function getfilelink() string      dim fso object, myfolder string     set fso = createobject("scripting.filesystemobject")      myfolder = thisworkbook.path & "\reports"       if fso.folderexists(myfolder) = false         fso.createfolder (myfolder)     end if      myfolder = myfolder & "\" & format(now(), "mmm_yyyy")      if fso.folderexists(myfolder) = false         fso.createfolder (myfolder)     end if      getfilelink = myfolder & "\mis " & format(now(), "dd-mm-yy hh.mm.ss") & ".xls"     set fso = nothing  end function

Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more