1. Home
  2. >>
  3. portal
  4. >>
  5. VBA and GMail through a company login portal

VBA and GMail through a company login portal

VBA and GMail through a company login portal

I have tried to understand this, but I cannot. I am working on a VBA project for a manufacturing setup, where forklift drivers need to be able to directly email updated material movement lists to employees documenting them in a new database system.

The idea is that there is a set of encrypted email addresses in a module and a single click sends the spreadsheet directly to the email after the driver selects the employee on duty in the particular shift.

The utility for drivers to pick up the employee on the move is quite simple, the following scrolls through 5 different employees, scattered over various shifts, but there is little hope of repeating the code:

Private Sub cboClerk_Change() With UserForm1.cboClerk If .ListIndex = 0 Then 'listindex goes from 0 to 4 UserForm1.lblEmail = "" ThisWorkbook.Sheets(1).Range("C1") = UserForm1.lblEmail ThisWorkbook.Sheets(1).Range("A1") = "Clerk on duty: First, Last" End If end with 

The next part is causing problems. There are a few examples of accessing gmail from VBA that I have tried to adapt, and this was the most promising solution that I could find and of which I do not claim any author:

 Sub ActivateGmail() Dim newMail As CDO.Message Set newMail = New CDO.Message 'enable SSL authentication newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True 'make SMTP authenticaion Enabled = true (1) newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'set the SMTP server and port details 'to get these details you can get on the settings page of your Gmail account newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 2 newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 587 'set your credentials of your Gmail account newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "" newMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword" 'update the configuration fields newMail.Configuration.Fields.Update 'set all email properties With newMail .Subject = "Test mail" .From = "" .To = "" .TextBody = "I gots it!" End With newMail.Send MsgBox ("Mail has been sent") 'set the newMail variable to nothing Set newMail = Nothing End Sub 

There is a company portal with a global ID and password that includes gmail login, which I believe prevents you from referencing gmail directly with the above solution. Attempting to log in from gmail.com redirects to the corporate login page, which has its own login credentials. I have temporarily done the following:

 Sub try6() ThisWorkbook.FollowHyperlink Address:="http://www.gmail.com", NewWindow:=True End Sub 

… Which works if the driver is fortunately already connected to gmail, but that would still require the manual work of sending an email. Due to the poor wifi in the factory I take care, the time accesses out and this is not a sufficient solution. As such, I am wondering how to embed a corporate login portal as part of a VBA gmail solution?

Thanks in advance.

Incredible. I think that I have understand.

First, credit to the author on this page for the code that looks similar to the first post: http://www.codekabinett.com/rdumps.php?Lang=2&targetDoc=send-email-access-vba-cdo

For reason:

 Public Sub sendmail() Dim mail As CDO.Message Dim config As CDO.Configuration Set mail = CreateObject("CDO.message") Set config = CreateObject("Cdo.configuration") config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort config.Fields(cdoSMTPServer).Value = "aspmx.l.google.com" config.Fields(cdoSMTPServerPort).Value = 25 '25 config.Fields(cdoSMTPAuthenticate).Value = cdoBasic 'cdoNTLM 'cdoBasic 'cdoNTLM config.Fields(cdoSendUserName).Value = "" 'domain is not gmail config.Fields(cdoSendPassword).Value = "mypassword" config.Fields.Update Set mail.Configuration = config With mail .To = "" .From = "" .Subject = "Hello" .TextBody = "Plain email with CDO" '.addattachment "Path" .Send End With Set config = Nothing Set mail = Nothing End Sub 

What cracked the problem was replacing smtp.gmail.com with aspmx.l.google.com . What it does is that it completely bypasses the company login portal and can send an email from the driver’s account without him even needing to be logged in. This is even better than I was hoping for.

The authentication does not have to be 465 or 587, which are Google’s output ports, because the row Config.fields.update will throw an error. It must be cdoBasic or cdoNTLM , both of whom work to send an email. I am not aware of any additional options other than these at present.

SMTPServerPort should (should?) Be 25.

Hopefully this works for anyone else facing similar problems.