[kc07]

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!

[scotty718]

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...

[scotty718]

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?

[kc07]

Of course I'm interested in your help! I'm trying to learn VB to make life easier so any help would be awesome.

[scotty718]

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?

[kc07]

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.

[scotty718]

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):

 
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:

 
Sub autologin()
Dim IE As Object, ipf As Object
Dim strURL As String
strURL = "http://www.myurl.com/"
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 = "http://www.myurl.com/"
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.

[kc07]

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?

[scotty718]

[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.