1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro for Outlook to open a website, download, extract and rename files

Discussion in 'Business Applications' started by kc07, May 12, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. kc07

    kc07 Thread Starter

    Joined:
    May 12, 2012
    Messages:
    4
    Hi guys,

    I've been trying to find info on creating this macro but I didn't have much luck. I want to create a macro (not a rule) that opens my browser to a specific website if a certain criteria in the email is met. So if I get an email from a certain sender, the macro would open chrome and go to a url and login. This url has only 3 input fields and two buttons and nothing else. Login ID, Password, and extension. Buttons are continue and cancel. I want to enter info for all 3 fields and hit enter to login. This is where I think it gets tricky. I want the macro to search the email body for certain text and match that up with the text in the content page chrome has opened after I've logged in. Then, have the macro open the link that has the same matching text. I'll then be in the next page where a new document is ready for me to download. I want to repeat this search step and match the email body text to the content page with downloadable links. Then, I want it to click on the link that contains the matching text. This click will download the file to my computer. I then want the macro to extract that file if it is in .zip and be able to rename the file with date and title through the VBA code.

    I'm not sure if the latter half is even possible but any help would be greatly appreciated!

    Thanks!
     
  2. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Welcome to the board!

    I am almost certain that all this is possible, even if you need to program outside of VBA a bit, or have VBA interact with some other programming language. However, its a big ask. There are a lot of components here and you would need to build this step-by-step, I think because its so involved.

    That said, I am kind of intrigued by the problem and willing to throw some resources at it. I'll do some initial research, but I may need specific access to the website to test and play...
     
  3. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    I've done enough research to realize this is most likely possible. It will take some effort. Are you interested in the help I could offer?
     
  4. kc07

    kc07 Thread Starter

    Joined:
    May 12, 2012
    Messages:
    4
    Of course I'm interested in your help! I'm trying to learn VB to make life easier so any help would be awesome.
     
  5. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Okay. Well, this ask is quite a big development effort, something a lot larger than is typically asked on these boards. I’m interested in pitching in on your efforts, because I will get to learn some new tricks as well, even though the coding is a lot and could potentially be a whole program that a developer gets paid to write.

    First thing I will do is break down the process in pseudo-code (English version of what you want) thanks to the fact that you wrote a pretty descriptive request. This will give us the steps to work through, piece-by-piece, in order to eventually solve the problem. The idea here is to get each part working, then move onto the next.

    1 – Set up Outlook code that checks the sender of all incoming mails and does something if that sender is a certain person
    2 – Have Outlook code open a webpage (preferably from Chrome browser)
    3 – Have Outlook code find the controls on the webpage for ID, Password, and Extension and fill them with text.
    4 – Have Outlook code find the control for Continue and press it.
    5 – Have Outlook find the each text string in a series of strings in the email body and search all links for a match, and click the link
    6 – Have Outlook code download the file located in each link
    7 – If file is .zip, extract and rename

    This is a great way to learn to program, as it gives you very easy, definable steps to accomplish and thus takes the daunting aspect of building a huge thing out of the equation. You learn how to do each part and then before you know it, your program is written and you’ve learned a heck of a lot along the way. Two other things worth mentioning. 1) This may be brokedn down even further to make it more defined and clear. 2) You may find these steps can be altered and made easier as you go along.

    Do you have any VBA knowledge at all as a starting point? Also, does it have to be in Chrome? IE appears to me much more easier to program with VBA?
     
  6. kc07

    kc07 Thread Starter

    Joined:
    May 12, 2012
    Messages:
    4
    Thanks for the reply. Breaking down the steps is indeed a good idea. sadly my VBA knowledge is limited but I am willing to read up on the language. I have a general idea of how programming works though. And no, IE should be fine.
     
  7. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Okay. I found the steps to a solution in about 15 minutes. I know you don't know VBA as well, but with some searching you can find out how to integrate these parts and ultimately add on to them.

    First, code to check NewMail in Outlook. Place in ThisOutlookSession as Module (in Outlook VBE):

    Code:
     
    Option Explicit
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim olFld As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Private Sub Application_NewMail()
    Set olApp = Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFld = olNS.GetDefaultFolder(olFolderInbox)
    olFld.Items.Sort "Received", False
    If TypeOf olFld.Items.GetFirst Is MailItem Then
        Set olMail = olFld.Items.GetFirst
        If InStr(1, olMail.SenderName, "Scott") > 0 Then
            MsgBox "Hello " & olMail.SenderName
        End If
     
    End If
    Set olMail = Nothing
    Set olFld = Nothing
    Set olNS = Nothing
    Set olApp = Nothing
    End Sub
     
    
    Next, separate code for loginng into URL and another code for looping through links:

    Code:
     
    Sub autologin()
    Dim IE As Object, ipf As Object
    Dim strURL As String
    strURL = "[URL]http://www.myurl.com/[/URL]"
    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate strURL
    IE.Visible = True
    'enter username and password
    Set ipf = IE.document.getElementById("username")
    ipf.Value = "myUser" 'fill in the text box
    Set ipf = IE.document.getElementById("password")
    ipf.Value = "myPass" 'fill in the text box
    Set ipf = IE.document.getElementById("submit")
    ipf.Click    'click the submit button
    End Sub
     
    Sub loop_through_links()
    Dim IE As Object, ipf As Object
    Dim strURL As String
    strURL = "[URL]http://www.myurl.com/[/URL]"
    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate strURL
    IE.Visible = True
    Dim lngCnt As Long
    For lngCnt = 1 To IE.document.Links.Length
        Debug.Print IE.document.Links(lngCnt)
    Next
    End Sub
    
    You'll need to write code to match your e-mail body text and pass it into the parameters, and then ultimately download and upzip files. I'll leave that to you... after all, how would you learn VBA if I did all the work! I will say that it is all very possible. If you get stuck, shout back with what you are stuck on, and I will help out.
     
  8. kc07

    kc07 Thread Starter

    Joined:
    May 12, 2012
    Messages:
    4
    Thanks for the help Scott, I ran the autologin macro and an error "Run-time error '91': Object variable or With block variable not set. on the line

    set ipf = ie.document.getelementbyid("")
    ipf.click

    I tried manually logging in with IE, but I realize I couldn't. I guess it doesn't support IE 8. So I've been trying to figure out the commands to use in chrome but with no luck. any ideas?
     
  9. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Code:
     set ipf = ie.document.getelementbyid("") [\CODE] 
    
    needs to have an id name between the quotes. Most likely it is submit, but perhaps the name of the button is different. If you view the source code behind the webpage you should be able to identify the id, or name, of the button.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1052972