PDA

View Full Version : Open emails from excel.



excelliot
02-08-2007, 12:30 AM
in one cell name of site e.g yahoo.com is given, in second cell user name to login email account is given & in third name password is mentioned. IS it possible o directly open this side & loging to email account using name mentioned there!!:think:

Simon Lloyd
02-08-2007, 05:53 AM
ExcelElliot, this reads as though you have collected names, log ins and passwords for e-mail accounts and is directly linked to this thread http://vbaexpress.com/forum/showthread.php?t=11132 if all the accounts are yours there are plenty of free sites or software out there to help you manage all your accounts automatically.

Regards,
Simon

malik641
02-08-2007, 09:26 AM
in one cell name of site e.g yahoo.com is given, in second cell user name to login email account is given & in third name password is mentioned. IS it possible o directly open this side & loging to email account using name mentioned there!!:think:
Yes.

Check out this discussion here (http://www.vbaexpress.com/forum/showthread.php?t=9810&highlight=Got+mail%3F).

I've modified the code since then to look like this. Which I'm pretty sure I've tested with yahoo:
Sub OpenMyGMAIL(MyLogin As String, MyPass As String, EmailSite As String)
If MyLogin = "" Or MyPass = "" Or EmailSite = "" Then Exit Sub
Dim ie As Object, oDoc As Object, oList As Object, oLists As Object
Dim ieForm As Variant, form As Variant
Dim WebPage As String
Dim i As Integer

Set ie = CreateObject("InternetExplorer.Application")

WebPage = EmailSite

ie.Visible = True

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
If ie.Document.Title = "Gmail" Then GoTo ExitHere
Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)
For Each form In ieForm
Debug.Print form.outerHTML, i
If LCase(form.ID) = "email" Or LCase(form.ID) = "username" Or LCase(form.ID) = "user" Then
form.innertext = MyLogin
ElseIf LCase(form.ID) = "passwd" Or LCase(form.ID) = "password" Then
form.innertext = MyPass
End If
i = i + 1
Next
ie.Document.forms(0).Submit

Do Until ie.ReadyState = 4
Loop

Debug.Print ie.Document.Title

ExitHere:
ie.Visible = True
Set ie = Nothing
End Sub

For your purpose you could use like this:
Public Sub Test()
Dim strUserName As String, strPassword As String, strEmailSite As String
strUserName = ActiveSheet.Range("A1").Text
strPassword = ActiveSheet.Range("A2").Text
strEmailSite = ActiveSheet.Range("A3").Text
Call OpenMyGMAIL(strUserName, strPassword, strEmailSite)
End Sub

With A1=Username, A2=Password, A3=EmailSite

Hope this helps :thumb

Charlize
02-09-2007, 03:38 AM
With some little changes it worked fine for me. Thank you Joseph for the revised code.

Charlize

xls
02-09-2007, 04:23 AM
Just Try this This will Serve your requirements..


Public CheckMailz Test()
Dim strUserName As String, strPassword As String, strEmailSite As String
strEmailSite = ActiveSheet.Range("A3").Text
strUserName = ActiveSheet.Range("B3").Text
strPassword = ActiveSheet.Range("C3").Text
Call OpenMyGMAIL(strUserName, strPassword, strEmailSite)
End Sub

Sub OpenMyGMAIL(MyLogin As String, MyPass As String, EmailSite As String)
Dim ie As Object, oDoc As Object
Dim ieForm As Variant
Dim WebPage As String

Set ie = CreateObject("InternetExplorer.Application")

WebPage = ActiveSheet.Range("A3").Text
ie.Visible = True
ie.navigate WebPage

'Loop until IE pages is fully loaded

Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

ieForm(6).innerText = ActiveSheet.Range("B3").Text
ieForm(7).innerText = ActiveSheet.Range("C3").Text
ie.Document.forms(0).Submit


End Sub
:thumb
All D Best.

Charlize
02-09-2007, 04:36 AM
ieForm(22).innerText = ActiveSheet.Range("B3").Text
ieForm(23).innerText = ActiveSheet.Range("C3").Text

malik641
02-09-2007, 08:34 AM
With some little changes it worked fine for me. Thank you Joseph for the revised code.

Charlize
No problem :thumb

Hey guys, I'm curious to know that the ID text was of what you changed in my code:

ieForm(22).ID
ieForm(23).ID

ieForm(6).ID
ieForm(7).ID

If you could, please. I'd like to refine the code some more :)

Charlize
02-09-2007, 12:37 PM
For Yahoo it was username and passwd (according to me). On the 22nd and 23td item the loop was executed (value of i). For the use of both Gmail and Yahoo I removed the line If ie.Document.Title = "Gmail" Then Goto ExitHerebecause it has no use (it's my humble opinion) when checking for multiple accounts. Also removed to exithere: line

Charlize

malik641
02-09-2007, 01:37 PM
If ie.Document.Title = "Gmail" Then Goto ExitHerebecause it has no use (it's my humble opinion) when checking for multiple accounts.
Charlize,

Yes, you are absolutely correct. I was using that line to check if I had already logged in. Thanks for bringing that up because I could just use "On Error Goto ExitHere" like:
Sub OpenMyGMAIL(MyLogin As String, MyPass As String, EmailSite As String)
If MyLogin = "" Or MyPass = "" Or EmailSite = "" Then Exit Sub
Dim ie As Object, oDoc As Object, oList As Object, oLists As Object
Dim ieForm As Variant, form As Variant
Dim WebPage As String
Dim i As Integer

On Error GoTo ExitHere

Set ie = CreateObject("InternetExplorer.Application")

WebPage = EmailSite

ie.Visible = True

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)
For Each form In ieForm
Debug.Print form.outerHTML, i
If LCase(form.ID) = "email" Or LCase(form.ID) = "username" Or LCase(form.ID) = "user" Then
form.innertext = MyLogin
ElseIf LCase(form.ID) = "passwd" Or LCase(form.ID) = "password" Then
form.innertext = MyPass
End If
i = i + 1
Next
ie.Document.forms(0).Submit

Do Until ie.ReadyState = 4
Loop

Debug.Print ie.Document.Title

ExitHere:
ie.Visible = True
Set ie = Nothing
End Sub:)

And I'm assuming you didn't have to explicitly call out the form numbers using this code? Or you just provided it to show which form numbers you ended up with in comparison to xls' form numbers?

And if you and xls were both calling out form numbers using the same site (i.e. yahoo), then I would suggest not to do that. It just doesn't seem dynamic enough to me.

Charlize
02-11-2007, 02:38 PM
Just tested the loop again and for Gmail it is indeed 6 and 7. For myself I use the loop because it's handy that with one routine you could check multiple accounts for new items. One problem that could arise are the names that are given to the formvariables. Maybe use external file to read possible names of variables per two

name
password
login
passwd
inlog
logpass
inlogid
checkpass

...

Charlize

ps. Or just looking for the most used names in those forms and hoping that most sitebuilders would use those variables.

malik641
02-11-2007, 08:38 PM
...Maybe use external file to read possible names of variables per two...
Great idea Charlize. :)

Haven't made it yet, but I'm thinking about making 2 text files that will list all the form IDs that you mentioned (one file for userID and another for password), with a delimeter of a carriage return. Then the procedure could read the text files for the IDs and maybe store them into arrays, then use them in the loop of the forms.

At the same time while this is happening, we can store the web page's form IDs into another array. And if none of the form IDs we listed are found, a message box could show up stating that it couldn't recognize the form IDs. The user will be given the option to add form IDs to the text files from the form IDs that were in the page itself. The addition of the form IDs to the text files can be done numeris ways (userform, input boxes, etc).

Like I said, haven't made anything yet (gotta study for a test), but just putting the idea out there.



Is there a standard for web page development? Like standard form ID names? :think:

xls
02-11-2007, 09:07 PM
Great idea Charlize. :)
Is there a standard for web page development? Like standard form ID names? :think:

Even i wondered same, if there would have something like that, then no prob wud hav arose.

xls
02-11-2007, 11:04 PM
I read this thread,

http://www.vbaexpress.com/forum/showthread.php?t=9810&highlight=Got+mail%3F

Is there anyway to get data, without opening web page???:think: