Outlook VBA 用于发送带附件的电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15278611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Outlook VBA for sending emails with attachment
提问by user2145517
I need to send almost 100 emails in one day to different people with different filenames. The code below works perfectly fine but the problem is that the files I have to attach should be dated one day previous. For example the date today is 7th march 2013 (7-03-13). I get the files RN2425 06-03-13.xls and these change every day. I want to lookup the one day previous files in particular directory D:\Reporting\Daily\RN2425\RN2425 (one day previous datestamp).xls
我需要在一天内向不同文件名的不同人发送近 100 封电子邮件。下面的代码工作得很好，但问题是我必须附加的文件的日期应该是前一天。例如，今天的日期是 2013 年 3 月 7 日 (7-03-13)。我得到文件 RN2425 06-03-13.xls，这些文件每天都在变化。我想在特定目录 D:\Reporting\Daily\RN2425\RN2425（前一天日期戳）中查找前一天的文件。xls
Please help me by using this code I need to change the dates in the filenames. I want this to be done automatically.
请使用此代码帮助我，我需要更改文件名中的日期。我希望这能自动完成。
Sub CreateEmail(Subject As String, Body As String, ToSend As String, CCs As String, FilePathtoAdd As String)
'write the default Outlook contact name list to the active worksheet
Dim OlApp As Object
Dim OlMail As MailItem
Dim ToRecipient As Variant
Dim CcRecipient As Variant
'Set OlApp = CreateObject("Outlook.Application")
'Set OlMail = OlApp.CreateItem(olMailItem)
Set OlApp = Application
Set OlMail = OlApp.CreateItem(olMailItem)
'For Each ToRecipient In Array("[email protected]", "[email protected]", "[email protected]")
'OlMail.Recipients.Add ToRecipient
OlMail.Recipients.Add ToSend
'Next ToRecipient
'For Each CcRecipient In Array("[email protected]", "[email protected]", "[email protected]")
'With OlMail.Recipients.Add(CcRecipient)
'.Type = 2
'End With
'Next CcRecipient
Dim Temp As Recipient
Set Temp = OlMail.Recipients.Add(CCs)
Temp.Type = olCC
'fill in Subject field
OlMail.Subject = Subject
OlMail.Body = Body
'Add the active workbook as an attachment
' OlMail.Attachments.Add "C:\Users\Ali\Desktop\Sentence Correction\Comparisons.pdf"
If FilePathtoAdd <> "" Then
OlMail.Attachments.Add FilePathtoAdd
End If
'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it
End Sub
Sub EmailIt()
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\RN2425 06-03-13.xls"
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\RN2425 06-03-13.xls"
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\RN2425 06-03-13.xls"
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\RN2425 06-03-13.xls"
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\RN2425 06-03-13.xls"
CreateEmail "This is Subject", "Body", "[email protected], [email protected]", "[email protected], [email protected]", "E:\Ali's Documents\AVSEQ03 Comp 1.avi"
End Sub
回答by niton
To get today's date in the correct format:
要以正确的格式获取今天的日期：
- Format(Date,"dd-mm-yy")
- 格式（日期，“日-月-年”）
To get yesterdays date:
获取昨天的日期：
- DateAdd("d", -1, Date)
- DateAdd("d", -1, 日期)
Putting it all together:
把它们放在一起：
- "E:\Ali's Documents\RN2425 " & Format(DateAdd("d", -1, Date),"dd-mm-yy") & ".xls"
- "E:\Ali's Documents\RN2425" & Format(DateAdd("d", -1, Date),"dd-mm-yy") & ".xls"
