How to add an attachment that changes names

[klewis10367]

How would you update this code to add an attachment that changes names when run. It goes to the same path but the log number changes everytime I run my application.

SMTPServer = "mbhqex07"

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "kevin.lewis@burroughs-chapin.com"
objEmail.To = "kevin.lewis@burroughs-chapin.com"
objEmail.Subject = "TSW Next Integration Failed for" & " " & Date-1
objEmail.Textbody = "Please click the attached link to view the TSW Next Integration Error File"
objEmail.AddAttachment "C:\Scripts\Output.txt"


objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= SMTPServer
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objEmail.Configuration.Fields.Update

objEmail.Send

 

[BobRodes]

I'm not quite sure what you mean by an "attachment that changes names". I'm going to assume that you mean the filename, in your case "Output.txt". In that case, you have to delete the existing attachment and add a new one:

with objEmail
   .Attachments.Delete 1 'it's a one-based array, this assumes you only have one attachment
   .AddAttachment myPath & "newfilename.txt"
End With

HTH

Bob

 

[klewis10367]

What I have is a text file named Log123..the next time I run the application it will be named Log124 and so on.I want to be able to attach the last log file.

 

[BobRodes]

I'm assuming that you're saying you want to attach the last log file and only the last log file, without attaching the file previous to it. So:

with objEmail
   .Attachments.Delete 1 'it's a one-based array, this assumes you only have one attachment
   .AddAttachment myPath & "Log124.txt"
End With

Where myPath is a string with the path in which your log files reside. You could also set up an incrementor, maybe in a static variable that would plug into your file name each time you called the proc that sends out the email.

I hope this clarifies. Feel free to ask further if not.

Bob

 

[klewis10367]

Thats the problem, I have many log files that are being generated by my app so the numbering of the log file is not always going to be incremented by one. It could be Log123 then Log145 the next time it is run But mypath is always pointing to the same location so I just want to be able to grab the last file based on datetime

 

[BobRodes]

Are you saying that, in your example, there will never exist a file between 123 and 145, or are you saying that they will exist but are created by different aspects of your application?

 

[klewis10367]

Created by different aspects.

 

[klewis10367]

This is the original code that I belive is in vb but I need it translated using objemail:

'Set the path to the log files. Edit this line as appropriate
sLogPath="Mbhqsql02\dataimport\TSWNextIntegrate\"
Set pFSO = CreateObject("Scripting.FileSystemObject")
Set pFolder = pFSO.GetFolder(sLogPath)
dtDateCreated=CDate("1/1/1900")
For Each File In pFolder.Files
If File.DateCreated > dtDateCreated then
dtDateCreated = File.DateCreated
sFileName=File.Name
End If
Next
'*******************************************
'************CREATE AND SEND THE E-MAIL*****
'*******************************************
'Edit the following line to indicate the e-mail address to
'which notification should be sent
sAddress = "kevin.lewis@burroughs-chapin.com"
'Set the subject line for the e-mail:
sSubject = "TSW Next Integration Error File" & " " & Date()-1
'Generate the body of the e-mail
'Edit the following text as appropriate
sBody = sBody & VbCrLf & "Please Click the Attached Link to View the Integration Errors File://" & sLogPath & sFileName
'Create an instance of Outlook & create an e-mail item
Set pOutlook=CreateObject("Outlook.Application")
Set pMAPI = pOutlook.GetNamespace("MAPI")
Set pMail = pOutlook.CreateItem(0)
'Set the recipient, subject & body of the e-mail
pMail.Recipients.Add(sAddress)
pMail.Subject = sSubject
pMail.Body = sBody

 

[BobRodes]

Ok, I'll assume that you have done most of the translation into CDO. If you want to have a unique number, just put it in a static variable:

Public Sub SendEmail()
static NextNum as Long

with CreateObject("CDO.Message")
   'your other Message object code
   .AddAttachment myPath & "Log" & cStr(NextNum)
   'and here
end with
NextNum = NextNum + 1
End Sub

Now, you have your application call this code exclusively to send emails.

If you're creating sequential log files by processes other than sending emails, you can just make a Function (put it in a standard module) and call it from anywhere in your application:

Public Function GetNextLogNum() As Long
static NextNum as Long
GetNextLogNum = NextNum
NextNum = NextNum + 1
End Function

HTH

Bob

 

[JerryKlmns]

klewis10367,

you could always find the file in a folder that is last modified!

Use the Scripting FileSystemObject and for each file check the part of the file name that does not change and keep track of the time that was last modified.

 

[jjames]

It appears that the 1st part of the original code does what your asking.

This is from your original code...

'Set the path to the log files. Edit this line as appropriate

sLogPath="Mbhqsql02\dataimport\TSWNextIntegrate\"

Set pFSO = CreateObject("Scripting.FileSystemObject")

Set pFolder = pFSO.GetFolder(sLogPath)

dtDateCreated=CDate("1/1/1900")

For Each File In pFolder.Files

   If File.DateCreated > dtDateCreated then

       dtDateCreated = File.DateCreated
       sFileName=File.[b]Path[/b]
       [i]'changed this from File.Name so we don't have to worry about the path later[/i]

   End If

Next

At this point, the variable sFileName contains the path and name of the newest file.

'your emailing code...

SMTPServer = "mbhqex07"

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "kevin.lewis@burroughs-chapin.com"
objEmail.To = "kevin.lewis@burroughs-chapin.com"
objEmail.Subject = "TSW Next Integration Failed for" & " " & Date-1
objEmail.Textbody = "Please click the attached link to view the TSW Next Integration Error File"
objEmail.AddAttachment [b]sFileName[/b]

objEmail.Configuration.Fields.Item _
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
objEmail.Configuration.Fields.Item _
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = SMTPServer 
objEmail.Configuration.Fields.Item _
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
objEmail.Configuration.Fields.Update

objEmail.Send