Consulting

Results 1 to 7 of 7

Thread: Specific functions for an Email Sender

  1. #1

    Specific functions for an Email Sender

    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?

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    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.

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    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!

  6. #6
    If you are using Exchange Server, you must use .

    .SentOnBehalfOfName = strAcc
    in place of
    .SendUsingAccount = oAccount
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •