Copy web content from Outlook to Excel using VBA

1
votes

My client receives an email with a one-time-access url each day (it can only be accessed once, and only on that day).

Content from this url is plain text formatted as a .csv (actually tilda sv) file, but that is not of relevance here.

The content is then (manually) copied and pasted into Excel sheet, where some (formatting) macros are applied (to get rid of tildas and put data into cells). Also, few of the first rows of the content are irrelevant, i.e. only lines 5-end are copied into Excel.

My question is the following - is it possible to write a VBA in Outlook to

  1. open the url provided in the given email

  2. copy the content from the webpage (and remove those first couple of lines)

  3. open Excel and paste it there (and apply formatting afterwards)

I found some answers on how to copy/paste content from the email itself into Excel sheet, but didn't manage to sort out how to do it from a url/webpage.

1
excelurlvbaoutlook
Have you tried saving the webpage and then reading the file? - enderland

1 Answers

0
votes

Yes it is possible

I am not expert on VBA, but I do little programing to reduce daily task

I can show you the steps:

  1. create rule that triggers VB script when email arrives.
  2. In the VB script in the outlook, write code that would open excel
  3. pass the connection string
  4. set the R1C1 as destination cell
  5. Save the file as date variant .xls

Make sure outlook is open. This would not work if outlook is not open