Specific functions for an Email Sender

[alundra828]

Hey Guys, I've been working in an Email sender that must meet very specific criteria.

I was wondering if any of you could help me meet said criteria? First a bit of back ground. We have a procedure set up that generates a file on the clients end, and it gets sent to us via an SFTP. We then have to email these files to another company (Its the only way they'll accept them...) But we get 100's of these files in a day, we have to send them one by one, and we have to send them over a secure connection using outlook.

Basically, I need an email to open, select one file at a time from a directory of many files, attach it to the email, select the 2nd and 3rd character of the file name and place it in the subject (this is a site identifier), send the email over a secure connection.

I know I can create a script that can send an email over a secure connection as I've done it before in VB and in Python, but can you help with a script that also does the above?

[gmayor]

If we can assume the same folder, and the same recipient each time, then this is pretty straightforward.
The following will attach each file to a new message with the subject as indicated. Test with only two or three files in the folder.
I have not included any code to handle the files in the folder after they have been sent.



Option Explicit

Sub ProcessFolder()
Const strPath As String = "C:\Path\" 'The path to the files
Dim strCharacter As String
Dim strFile As String
strFile = Dir$(strPath & "*.*") 'any file in the folder
While strFile <> ""
CreateMessage strPath & strFile, Mid(strFile, 2, 2)
DoEvents
strFile = Dir$()
Wend
lbl_Exit:
Exit Sub
End Sub

Sub CreateMessage(strAtt As String, strChar As String)
Dim olMail As MailItem
Dim olAttach As Attachment
Dim olInsp As Inspector
Dim wdDoc As Object
Dim oRng As Object
Set olMail = Outlook.CreateItem(olMailItem)
With olMail
.BodyFormat = olFormatHTML
.To = "someone@somewhere.com" 'The address of the recipient
.Subject = strChar
Set olAttach = .Attachments.Add(strAtt)
.Display
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range(0, 0)
oRng.Text = "Please find attached file: " & olAttach.FileName ' The message text
'.sEnd '- Restore after testing
End With
lbl_Exit:
Set olMail = Nothing
Set olAttach = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Exit Sub
End Sub

[alundra828]

Hey Gmayor, great work as always!

Just a few things that I can't quite work out.

1. This company is insisting I don't have any text in the body of the email (I don't really know why.)
2. I didn't see any lines in there about taking the 2nd and 3rd character of the file name and putting it into the subject? Is that even possible? And if it is in there please forgive my ignorance!
3. It looks like this is just sending out an ordinary email, I need it to go over a secure connection. So it needs to open the directory, see there are 10 (for example) files in there, and cycle through them sending 1 secure email for each file. Is there some sort of way to insert what port it goes through? Or, ideally, a way to change the email account it sends from?

I have 2 email accounts, my work one, and the account which sends the files securely.

[gmayor]

1. To remove the text in the message body, delete the lines



Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range(0, 0)
oRng.Text = "Please find attached file: " & olAttach.FileName ' The message text


and



Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing


2. You didn't test it? The bold part of

CreateMessage strPath & strFile, Mid(strFile, 2, 2)

should collect the two filename characters for the message subject

3. The message is sent from the default account. You can send it instead from a named account e.g.



Sub CreateMessage(strAtt As String, strChar As String)
Dim oAccount As Account
Dim olMail As MailItem
Dim olAttach As Attachment
Const strAcc = "myaccount@somewhere.com" 'the name of the account to use
For Each oAccount In Application.Session.Accounts
If oAccount.DisplayName = strAcc Then
Set olMail = Outlook.CreateItem(olMailItem)
With olMail
.SendUsingAccount = oAccount
.To = "someone@somewhere.com"
.Subject = strChar
.Body = "" ' clear the body
Set olAttach = .Attachments.Add(strAtt)
.Display
'.sEnd '- Restore after testing
End With
Exit For
End If
Next
lbl_Exit:
Set olMail = Nothing
Set olAttach = Nothing
Set oAccount = Nothing
Exit Sub
End Sub

[alundra828]

Hey Gmayor, I have a feeling this is hitting our exchange server and bombing. Let me do a little testing and i'll get back to you. Thanks again!

[gmayor]

If you are using Exchange Server, you must use .


.SentOnBehalfOfName = strAcc in place of

.SendUsingAccount = oAccount

[alundra828]

Hey Gmayor! Finally got it working. I also need to remember that back slashes on the end of paths are important... My bad! I've done some testing on personal email addresses over normal email connections and it is working pretty much flawlessly. Going to give it a shot over the secure connection when I get the okay from the higher ups.

I'll let you know if there are any problems! I'm going to go ahead and set this thread to solved as well