Ankur Kumar
New Member
Hi,
while i am sending a mail using VBA coding i need it will automatically pick yesterday date not current date , i am sharing with you VBA coding please help
Sub Mail_Bank_Report()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "<H4><B>Dear All,</B></H4>" & _
"Please find Sales Detail as attached.<br>"
'Change only Mysig.htm to the name of your signature
SigString = Environ("appdata") & _
"\Microsoft\Signatures\Ankur.htm"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
With OutMail
.Display
.To = "jaswant.singh@vccircle.com;virender.manral@vccircle.com"
.CC = "accounts@vccircle.com"
.BCC = ""
.Subject = "VCC BANK REPORT DATED" & " " & Format(Date, "dd/MM/yyyy")
.HTMLBody = strbody & "<br>" & Signature
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
while i am sending a mail using VBA coding i need it will automatically pick yesterday date not current date , i am sharing with you VBA coding please help
Sub Mail_Bank_Report()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "<H4><B>Dear All,</B></H4>" & _
"Please find Sales Detail as attached.<br>"
'Change only Mysig.htm to the name of your signature
SigString = Environ("appdata") & _
"\Microsoft\Signatures\Ankur.htm"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
With OutMail
.Display
.To = "jaswant.singh@vccircle.com;virender.manral@vccircle.com"
.CC = "accounts@vccircle.com"
.BCC = ""
.Subject = "VCC BANK REPORT DATED" & " " & Format(Date, "dd/MM/yyyy")
.HTMLBody = strbody & "<br>" & Signature
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
