通过企业登录门户的VBA和GMail [英] VBA and GMail through a corporate login portal

问题描述

我一直试图找出答案，但我不能.我正在为制造环境进行VBA项目，在该项目中，叉车司机需要能够直接通过电子邮件将物料移动的更新列表发送给店员，以在新的数据库系统中对其进行记录.

这个想法是，在一个模块中有一组硬编码的电子邮件地址，并且在驾驶员选择了特定班次的值班员之后，单击一下即可将电子表格直接发送到电子邮件中.

驾驶员在轮班中挑选文员的实用程序非常容易，以下内容将滚动到5个不同的职员中，这些职员分散在不同的班次中，但是重复代码毫无意义:

 私有子cboClerk_Change()与UserForm1.cboClerk如果.ListIndex = 0，则'listindex从0变为4UserForm1.lblEmail ="[e-mail]"ThisWorkbook.Sheets(1).Range("C1")= UserForm1.lblEmailThisWorkbook.Sheets(1).Range("A1")=值班文员:第一，最后"万一结束于 

下一部分导致了我的问题.我尝试改编了许多从VBA访问gmail的示例，这是我能够找到的最有前途的解决方案，并且我不主张任何作者身份:

  Sub ActivateGmail()将newMail昏暗化为CDO.Message设置newMail = New CDO.Message'启用SSL身份验证newMail.Configuration.Fields.Item _("http://schemas.microsoft.com/cdo/configuration/smtpusessl")=真'使SMTP身份验证已启用= true(1)newMail.Configuration.Fields.Item _("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")= 1'设置SMTP服务器和端口详细信息'要获取这些详细信息，您可以在Gmail帐户的设置页面上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")= 2newMail.Configuration.Fields.Item _("http://schemas.microsoft.com/cdo/configuration/sendusing")= 587'设置您的Gmail帐户的凭据newMail.Configuration.Fields.Item _("http://schemas.microsoft.com/cdo/configuration/sendusername")="[e-mail]"newMail.Configuration.Fields.Item _("http://schemas.microsoft.com/cdo/configuration/sendpassword")="mypassword"'更新配置字段newMail.Configuration.Fields.Update'设置所有电子邮件属性使用newMail.Subject =测试邮件".From ="[e-mail]".To ="[e-mail]".TextBody =我明白了！"结束于newMail.SendMsgBox(邮件已发送")'将newMail变量设置为空设置newMail = Nothing结束子 

有一个具有全局ID和密码的企业门户，其中包括对gmail的访问权限，我认为这使我无法通过上述解决方案直接引用gmail.尝试从gmail.com登录会重定向到公司登录页面，该页面具有自己的登录凭据.我暂时采取了以下措施:

  Sub try6()ThisWorkbook.FollowHyperlink地址:="http://www.gmail.com"，NewWindow:= True结束子 

...如果幸运的是，驱动程序已经登录gmail，则可以使用，但是仍然需要手动发送电子邮件.由于我工作所在工厂的wifi网络不佳，登录超时，这不是一个足够的解决方案.因此，我想知道如何将企业登录门户纳入VBA gmail解决方案的一部分?

谢谢.

解决方案

令人难以置信.我想我知道了.

首先，在此页面上将类似于第一篇文章的代码归功于作者:

使问题更棘手的是将 smtp.gmail.com 替换为 aspmx.l.google.com .它的作用是完全绕过公司登录门户，甚至可以从驾驶员的帐户发送电子邮件，而无需驾驶员登录.甚至比我期望的要好.

身份验证不得使用465或587(这是Google的传出端口)，因为 Config.fields.update 行将引发错误.它必须是 cdoBasic 或 cdoNTLM ，两者都可以发送电子邮件.目前，除了这些以外，我没有其他选择.

SMTPServerPort应该(必须为25).

希望这对其他遇到类似问题的人都有效.

I have been trying to figure this out, but I cannot. I'm working on a VBA project for a manufacturing setting, where forklift drivers need be able to directly email updated lists of material movements to clerks who document them in a new database system.

The idea is that there is a set of hard-coded email addresses in a Module and one click sends the spreadsheet directly to the email after the driver selects the clerk on duty in the particular shift.

The utility for drivers to pick the clerk on the shift is easy enough, the following rolls through 5 different clerks, scattered on various shifts, but there is little point in repeating the code:

Private Sub cboClerk_Change()

With UserForm1.cboClerk

    If .ListIndex = 0 Then 'listindex goes from 0 to 4

        UserForm1.lblEmail = "[e-mail]"
        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 me issues. There are a number of examples for accessing gmail from VBA that I've tried adapting, and this was the most promising solution that I was able to locate, and of which I claim no authorship whatsoever:

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") = "[e-mail]"

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 = "[e-mail]"
    .To = "[e-mail]"
    .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 corporate portal with a global ID and password that includes access to gmail, which I believe prevents me from referring to gmail directly with the above solution. Attempting to login from gmail.com redirects to the corporate login page, which has its own login credentials. I've temporarily resorted to the following:

Sub try6()

ThisWorkbook.FollowHyperlink Address:="http://www.gmail.com", NewWindow:=True

End Sub

...which works should the driver be luckily already logged into gmail, but it would still necessitate the manual labour of sending an email. Because of lacklustre wifi at the factory I work in, logins time out and this is not a sufficient solution. As such, I am wondering how to incorporate a corporate login portal as a part of a VBA gmail solution?

Thank you in advance.

解决方案

Unbelievable. I think I figured it out.

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

To wit:

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 = "[e-mail]" 'domain is not gmail
config.Fields(cdoSendPassword).Value = "mypassword"
config.Fields.Update

Set mail.Configuration = config

With mail

    .To = "[e-mail]"
    .From = "[e-mail]"
    .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 it bypasses the corporate login portal entirely and can send an email from the driver's account without him even needing to be logged into it. That's even better than I was hoping for.

Authentication must not be 465 or 587, which are Google's outgoing ports, because the Config.fields.updateline will throw an error. It must be either cdoBasic or cdoNTLM, both of which work in sending an email. I'm not aware of any additional options than these, currently.

SMTPServerPort should (must?) be 25.

Hopefully this works for anyone else facing similar issues.

这篇关于通过企业登录门户的VBA和GMail的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！