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.
- VBA code to submit email from secondary email address in Outlook
- Ron de Bruin’s VBA excel script for bulk email with single files does not work with VLOOKUP
- How to submit an email with an excel attachment
- What is BIRT and Excel doing with >?
- Visual Basic – How to select cells from a column or return error
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:
- Problems with sending an Excel document (converted to HTML / PHP) doc to Email via the mail () function
- The workbook is automatically sent to recipients but also sends emails to unlisted emails
- jQuery DataTables – Export as an Excel attachment and send the mail
- Cannot get the code for the email file as an attachment once the submit button is clicked
- How to create an Excel table that automatically sends an email to someone on their birthday?
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
What cracked the problem was replacing
The authentication does not have to be 465 or 587, which are Google’s output ports, because the row
SMTPServerPort should (should?) Be 25.
Hopefully this works for anyone else facing similar problems.
- submit an email with attachment (problem is the file size is 0k)
- Using VB.NET to copy the Excel range (a table) to a set of Outlook emails
- Powershell script to extract and rename .xls from perspective
- Search for an email address from a cell
- Change the order of the cells to submit e-mail, then change them
- VBA Email with pasted chart and text in body
- EXCEL VBA hides e-mail and e-mail sheets
- multipart mail with inline attachment: part of the excel sheet treated as an image