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

node.js - Getting the socket id,user id pair of a logged in user(s) -

i have web application done using node js , in it,i have authentication system requires users log in before can access pages. have 2 pages,the homepage , updates page. from read docs,a new socket id issued when user opens tab in browser. me being admin,i know socket id of logged in user , more so,the socket id,logged in user id pair logged in users. what method can use socket id,logged in user id pair make sure broadcast realtime info users online?.
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