i have email contains following. there more html text above , below screenshot, i'm hoping can leave stuff untouched. have excel command can import account , value need (in scenario, "2222222222222") excel.
my next step make work have code can delete text after word "deadline" until word "term" , insert data email excel information need displayed in table. (let's excel info needed stored in cells a1 , b1)
this taking place after click "foward email" button in outlook, , message appears message history under signature. here code have far:
sub asset_email() dim olapp outlook.application dim olns namespace dim fldr mapifolder dim olmail outlook.mailitem dim integer dim olmsg outlook.mailitem dim r range dim strlocation string dim o outlook.application dim strbody string dim objinspector object 'dim olatt outlook.attachments 'set olatt = olmsg.attachments set r = activesheet.buttons(application.caller).topleftcell range(cells(r.row, r.column), cells(r.row, r.column)).select set olapp = new outlook.application set olns = olapp.getnamespace("mapi") set fldr = olns.getdefaultfolder(olfolderinbox).folders("asset notifications macro") = 1 'set objinspector = olmail.getinspector each olmail in fldr.items if instr(olmail.body, activecell.offset(rowoffset:=0, columnoffset:=-3).value) <> 0 olmail.display if instr(olmail.body, "mandatory event: no responses required this") strbody = "<body style=font-size:11pt;font-family:calibri>team,<br><br>" & _ "please see notice below regarding " & _ activecell.offset(rowoffset:=0, columnoffset:=-2).value & _ ".<br><br> informational purposes , no action required.<br><br>" & _ "thank you!" const oltxt = 0 dim omail outlook.mailitem dim spath string dim dtdate date dim sname string sname = olmail.subject dtdate = olmail.receivedtime sname = format(dtdate, "yyyymmdd", vbusesystemdayofweek, _ vbusesystem) & format(dtdate, "-hhnnss", _ vbusesystemdayofweek, vbusesystem) & "-" & sname & ".txt" olmail.saveas "z:\my documents\emails\" & "email" & ".txt", oltxt 'olmail.saveas "z:\my documents\emails" & format(now, "yyyymmddhhmmss") & ".txt", oltxt olmail.forward .to = activecell.offset(columnoffset:=-1) .display sendkeys ("%") sendkeys ("7") 'call sleep application.wait (now + timevalue("0:00:03")) .htmlbody = strbody & "<br>" & .htmlbody .htmlbody = replace(.htmlbody, activecell.offset(rowoffset:=0, columnoffset:=-3).value, "<font style=" & chr(34) & "background-color: yellow" & chr(34) & ">" & activecell.offset(rowoffset:=0, columnoffset:=-3).value & "</font>") end end if if instr(olmail.body, "warning: response required") strbody = "<body style=font-size:11pt;font-family:calibri>team,<br><br>" & _ "please see notice below regarding " & _ activecell.offset(rowoffset:=0, columnoffset:=-2).value & _ ".<br><br> if client wishes make election, need call csg before gs deadline indicated on notice.<br><br>" & _ "thank you!" dtdate = olmail.receivedtime sname = format(dtdate, "yyyymmdd", vbusesystemdayofweek, _ vbusesystem) & format(dtdate, "-hhnnss", _ vbusesystemdayofweek, vbusesystem) & "-" & sname & ".txt" olmail.saveas "z:\my documents\emails\" & "email" & ".txt", oltxt 'olmail.saveas "z:\my documents\emails" & format(now, "yyyymmddhhmmss") & ".txt", oltxt olmail.forward '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'my sad attempt @ trying make work dim s string s = olmail.body dim indexofthey integer indexofthey = instr(1, s, "term") dim finalstring string finalstring = right(s, len(s) - indexofthey + 1) olmail.body = finalstring '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' .to = activecell.offset(columnoffset:=-1) .display sendkeys ("%") sendkeys ("7") 'call sleep application.wait (now + timevalue("0:00:03")) .htmlbody = strbody & "<br>" & .htmlbody .htmlbody = replace(.htmlbody, activecell.offset(rowoffset:=0, columnoffset:=-3).value, "<font style=" & chr(34) & "background-color: yellow" & chr(34) & ">" & activecell.offset(rowoffset:=0, columnoffset:=-3).value & "</font>") end end if end if next end sub can code can accomplish this?
Comments
Post a Comment