跟进使用VBA发送电子邮件 [英] Follow up about using VBA to send e-mail

问题描述

这是关于使用VBA发送电子邮件的几条消息的后续操作。

大多数建议都使用Outlook，CDO或MAPI：

 设置appOL = CreateObject（Outlook.Application）
 
设置msgOne = CreateObject（CDO.Message） 
 
设置mapi_session =新的MSMAPI.MAPISession 
  

但显然Outlook将需要我要更改我们的工作组安全设置，CDO和MAPI将需要我添加一个DLL或其他东西。

我正在尝试使用Excel来组织工作中的组作业，我不能以任何方式修改别人的电脑。

从Excel宏发送电子邮件是否有更简单的方法？

我所需要的只是邮件正文中没有附件的文本块。

我一直在通过Google，MSDN和StackOverflow整个星期，我被困在一个缓慢的船无处。

解决方案

建立在Marc提到的，这是一个

  Option Explicit 
 
声明函数ShellExecute Lib shell32.dll别名ShellExecuteA（ByVal hwnd As Long，_ 
 ByVal lpOperation As String，ByVal lpFile As String，ByVal lpParameters As String，_ 
 ByVal lpDirectory As String，ByVal nShowCmd As Long） As Long 
 
 Sub SendMail（）
 Dim objMail As String 
 Dim oMailSubj，oMailTo，oMailBody As String 
 
错误GoTo Whoa 
 
 oMailSubj =您的主题在这里
 oMailTo =[e-mail]
 oMailBody =BLAH BLAH !!!! 
 objMail =mailto：& oMailTo& ？subject =& oMailSubj& & body =& oMailBody 
 
 ShellExecute 0，vbNullString，objMail，vbNullString，vbNullString，vbNormalFocus 
 
 Application.Wait（Now + TimeValue（0:00:03））
 Application.SendKeys％s
 
退出Sub 
哇：
 MsgBox Err.Description 
 End Sub 
  pre> 
 
 
  FOLLOWUP  
 
 
 
 
 
 
感谢信息。我已经排除了ShellExecute，因为它将整个参数字符串限制为250个字符，我需要大约2000的消息。但它看起来像SE是唯一可以在我的情况下工作的选项。 -  Humanoid1000 7小时前
 
 
 
 
 
 
这是可怕我喜欢JFC说的那种方式!!! ）我在下面提到的评论中提到的方式很漂亮:) BTW我只有Outlook作为我的默认客户端，所以我已经测试过了。 p> 
 
 
 代码 
  Option Explicit 
 
声明函数ShellExecute Libshell32.dll别名ShellExecuteA（ByVal hwnd As Long，_ 
 ByVal lpOperation As String，ByVal lpFile As String，ByVal lpParameters As String，_ 
 ByVal lpDirectory As String，ByVal nShowCmd As Long）As Long 
 
声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
 
 Sub SendMail（）
 Dim objMail As String 
 Dim oMailSubj As String，oMailTo As String 
 Dim i As Long 
 Dim objDoc As Object，objSel As Object，objOutlook As Object 
 Dim MyData As String，strData（） as String 
 
 On Error GoTo Whoa 
 
'~~>打开具有正文的txt文件，一次读取
打开C：\Users\Siddharth Rout\Desktop\Sample.Txt对于二进制为＃1 
 MyData =空格$（LOF（1））
获取＃1，，MyData 
关闭＃1 
 strData（）= Split（MyData，vbCrLf）
 
睡眠300 
 
 oMailSubj =你的主题在这里
 oMailTo =[e-mail]
 objMail =mailto：& oMailTo& ？subject =& oMailSubj 
 
 ShellExecute 0，vbNullString，objMail，vbNullString，vbNullString，vbNormalFocus 
 
睡眠300 
 
设置objOutlook = GetObject（，Outlook.Application ）
'~~>从打开的Outlook项目中获取Word.Selection 
设置objDoc = objOutlook.ActiveInspector.WordEditor 
设置objSel = objDoc.Windows（1）.Selection 
 
 objDoc.Activate 
 
睡眠300 
 
对于i = LBound（strData）到UBound（strData）
 objSel.TypeText strData（i）
 objSel.TypeText vbNewLine 
 Next i 
 
 Set objDoc = Nothing 
设置objSel = Nothing 
 
'~~>取消注释以下实际发送电子邮件
'Application.Wait（Now + TimeValue（0:00:03））
'Application.SendKeys％s
 
退出Sub 
 Whoa：
 MsgBox Err.Description 
 End Sub 
  
  SNAPSHOT  
 
 
 
具有消息的文本文件
 
 
 
  
 
 
 
发送之前发送电子邮件
 
 
 
  
 
This is a follow up to several messages about using VBA to send e-mail.

Most suggestions use either Outlook, CDO, or MAPI:
Set appOL = CreateObject("Outlook.Application") 

Set msgOne = CreateObject("CDO.Message") 

Set mapi_session = New MSMAPI.MAPISession
But apparently Outlook will require me to change our workgroup security settings, and CDO and MAPI will require me to add a DLL or something.

I'm trying to use Excel to organize group assignments at work and I can't modify the other people's computers in any way.

Is there a simpler way to send e-mails from an Excel macro?

All I need is a block of text in the body of the message with no attachments.

I've been plowing through Google, MSDN & StackOverflow all week and I'm stuck on a slow boat to nowhere.
解决方案
Building up on what Marc mentioned, Here is a tried and tested version.
Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub SendMail()
    Dim objMail As String
    Dim oMailSubj, oMailTo, oMailBody As String

    On Error GoTo Whoa

    oMailSubj = "YOUR SUBJECT GOES HERE"
    oMailTo = "[e-mail]"
    oMailBody = "BLAH BLAH!!!!"
    objMail = "mailto:" & oMailTo & "?subject=" & oMailSubj & "&body=" & oMailBody

    ShellExecute 0, vbNullString, objMail, vbNullString, vbNullString, vbNormalFocus

    Application.Wait (Now + TimeValue("0:00:03"))
    Application.SendKeys "%s"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub
FOLLOWUP

  

    

      
Thanks for the info. I had already ruled out ShellExecute because it limits the entire parameter string to 250 char and I need about 2000 for the message. But it's looking like SE is the only option that will actually work in my case. – Humanoid1000 7 hours ago
    

  

Here is the "Horrible (I love the way JFC says that!!!)" way I mentioned below in the comments which works beautifully :) BTW I have only Outlook as my default client so I have tested it with that.

CODE
Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub SendMail()
    Dim objMail As String
    Dim oMailSubj As String, oMailTo As String
    Dim i As Long
    Dim objDoc As Object, objSel As Object, objOutlook As Object
    Dim MyData As String, strData() As String

    On Error GoTo Whoa

    '~~> Open the txt file which has the body text and read it in one go
    Open "C:\Users\Siddharth Rout\Desktop\Sample.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    Sleep 300

    oMailSubj = "YOUR SUBJECT GOES HERE"
    oMailTo = "[e-mail]"
    objMail = "mailto:" & oMailTo & "?subject=" & oMailSubj

    ShellExecute 0, vbNullString, objMail, vbNullString, vbNullString, vbNormalFocus

    Sleep 300

    Set objOutlook = GetObject(, "Outlook.Application")
    '~~> Get a Word.Selection from the open Outlook item
    Set objDoc = objOutlook.ActiveInspector.WordEditor
    Set objSel = objDoc.Windows(1).Selection

    objDoc.Activate

    Sleep 300

    For i = LBound(strData) To UBound(strData)
        objSel.TypeText strData(i)
        objSel.TypeText vbNewLine
    Next i

    Set objDoc = Nothing
    Set objSel = Nothing

    '~~> Uncomment the below to actually send the email
    'Application.Wait (Now + TimeValue("0:00:03"))
    'Application.SendKeys "%s"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub
SNAPSHOT

Text File which has the message



Email just before it is sent



                        
这篇关于跟进使用VBA发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！