Sending Email - Picking Up Attachment based on file path being part of the current date

[NICK1978]

Good Afternoon,

I would like some help with a script for sending email and picking up an attachment file.

The problem is that folder is in the format of a date - YYYYMMDD so I need something that calculates the date and builds it into the file path

' Constants
Const cSmtpUser = "XXX\Server"                                ' *** MAKE CHANGES HERE ***
Const cSmtpPassword = "Password"                            ' *** MAKE CHANGES HERE ***
Const cSmtpServer = "smtp.SERVER"                          ' *** MAKE CHANGES HERE ***
Const cSmtpPort = 25                                       ' *** MAKE CHANGES HERE *** (25, 465, 587 common)
Const cFromEmail = "admin@example.uk"                      ' *** MAKE CHANGES HERE ***
Const cToEmail = "nick@example.uk"                        ' *** MAKE CHANGES HERE ***
Const cSubject = "**** REMINDER - Yard / Workshop Inspection Due ****"                     ' *** MAKE CHANGES HERE ***

' CDO Constants needed to send email
Const cCdoSendUsingPickup = 1   'Send message using the local SMTP service pickup directory.
Const cCdoSendUsingPort = 2     'Send the message using the network (SMTP over the network).
Const cCdoAnonymous = 0         'Do not authenticate
Const cCdoBasic = 1             'basic (clear-text) authentication
Const cCdoNTLM = 2              'NTLM
Const cCdoSendUsingMethod        = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing"[/URL]
Const cCdoSMTPServer             = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver"[/URL]
Const cCdoSMTPServerPort         = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport"[/URL]
Const cCdoSMTPConnectionTimeout  = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"[/URL]
Const cCdoSMTPAuthenticate       = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"[/URL]
Const cCdoSendUserName           = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername"[/URL]
Const cCdoSendPassword           = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword"[/URL]
Const cCdoSmtpUseSsl             = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpusessl"[/URL]

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2


' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get a handle to the config object and it's fields
Set objConfig = CreateObject("CDO.Configuration")

' Set config fields we care about
With objConfig.Fields
    .Item(cCdoSendUsingMethod)       = cCdoSendUsingPort
    .Item(cCdoSMTPServer)            = cSmtpServer
    .Item(cCdoSMTPServerPort)        = cSmtpPort
    .Item(cCdoSMTPConnectionTimeout) = 60
    .Item(cCdoSMTPAuthenticate)      = cCdoBasic
    .Item(cCdoSendUserName)          = cSmtpUser
    .Item(cCdoSendPassword)          = cSmtpPassword
    .Item(cCdoSmtpUseSsl)            = True
    .Update
End With

' Get date in YYYYMMDD format
strDate = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)

' Create a new message
Set objMessage = CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig

' Populate message fields and send it
With objMessage
    .To       = cToEmail
    .From     = cFromEmail
    .Subject  = cSubject
    .TextBody = "**** REMINDER - Yard / Workshop Inspection Due ****" & vbNewLine & vbNewLine & "Please ignore this e-mail if you have already completed the yard & workshop inspection this week." & vbNewLine & vbNewLine
    .AddAttachment "D:\iauditor_exports_folder\Audits\" & strDate & "\WYIN*.pdf"	
    .Send
End With

I would like some help building the file path for the attachment - C:\Test\20190215\test.pdf

The date will change every day - so I need to put something in the script that calculates the date in the format YYYYMMDD and places it in the file path

 

[SkipVought]

Format(Date, “yyyymmdd”)

However, use your keyboard QUOTES rather than my querky iPad quotes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Looks like VbScript, rather than VB or VBA, and VBScript doesn't support the Format function. It does support FormatDate, but FormatDate only supports a very limited set of date formats; yyyymmdd is not one of them ... but you can fix this with thread329-1570966