Consulting

Results 1 to 9 of 9

Thread: vba check before sending email if attachment is present

  1. #1
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location

    vba check before sending email if attachment is present

    Greetings,
    I found this code online and it's not working for me (Outlook 2016)
    I have set all macros to allow in the trust center and inserted the code inside Developer-visual basic-this outlook session.
    ' Declare a variable to save the body content.
    Dim strBody As String
     
    ' Conver the body content to lowercase.
    strBody = LCase(Item.Body)
     
    ' /* Check if the body contains the keyword "attach". */
    If Not strBody Like "*attach*" Then
    Exit Sub
    End If
     
     
     
    ' /* Check the number of attachment(s). */
    If Item.Attachments.Count = 0 Then
    MsgBox "No Attachment currently.", vbExclamation, "Message"
    cancel = True
    End If
    End Sub
    Would someone be so kind and tell me if I’m doing it wrong or is there some thing wrong with the code.

    Bonus question: If I would like to add multiple “key words” do I separate them with -> ;
    Best regards

  2. #2
    You will need a different approach if you want to use multiple keywords e.g. as follows, however the code has a fundamental flaw as it stands, which you may not have considered - and that relates to what Outlook considers as attachments when processed using VBA. If, for example, you have the message formatted as HTML and you have signature with a graphic (as I do) then that will be seen as an attachment - similarly any images in the message (even emoticon images) will add to the attachment count, so you cannot reply on searching for a message count of 0.

    As it seems likely that you are looking for the presence of a particular attachment, then it would be better to test for that attachment (or an attachment of that type) rather than simply rely on a count.


    Option Explicit
    
    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
        ' Declare a variable to save the body content.
    Dim strBody As String
    Dim vKey As Variant
    Dim i As Long
    Dim bFound As Boolean
    Const strKeyWords As String = "attach|word2|word3|etc" 'use the words (or parts of words) to search for separated by the pipe character '|'
        Set Item = ActiveExplorer.Selection.Item(1)
    
        ' Convert the body content to lowercase.
        strBody = LCase(Item.Body)
        ' Check if the body contains any of the keywords
        vKey = Split(strKeyWords, "|")
        For i = 0 To UBound(vKey)
            If InStr(1, strBody, vKey(i)) > 0 Then
                MsgBox vKey(i) & " found" 'Optional for testing
                bFound = True
                Exit For
            End If
        Next i
        If Not bFound = True Then
            GoTo lbl_Exit
        End If
        ' /* Check the number of attachment(s). */
        If Item.Attachments.Count = 0 Then
            MsgBox "No Attachment currently.", vbExclamation, "Message"
            Cancel = True
        End If
    lbl_Exit:
        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
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Greetings,
    Ok I’ve tested your macro here is what I have found out:

    1. It gets triggered by an empty email with or without my signature (the signature doesn’t include any of the keywords)
    2. The warning massage works
    3. After attaching the attachment it still reports that there is no attachments

    Any idea what’s going on?

  4. #4
    There was a line in my code left from testing that should not have been present. See the following version instead:
    Option Explicit
    
    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
         ' Declare a variable to save the body content.
        Dim strBody As String
        Dim vKey As Variant
        Dim i As Long
        Dim bFound As Boolean
        Const strKeyWords As String = "attach|word2|word3|etc" 'use the words (or parts of words) to search for separated by the pipe character '|'
         
         ' Convert the body content to lowercase.
        strBody = LCase(Item.Body)
         ' Check if the body contains any of the keywords
        vKey = Split(strKeyWords, "|")
        For i = 0 To UBound(vKey)
            If InStr(1, strBody, vKey(i)) > 0 Then
                MsgBox vKey(i) & " found" 'Optional for testing
                bFound = True
                Exit For
            End If
        Next i
        If Not bFound = True Then
            GoTo lbl_Exit
        End If
         ' /* Check the number of attachment(s). */
        If Item.Attachments.Count = 0 Then
            MsgBox "No Attachment currently.", vbExclamation, "Message"
            Cancel = True
        End If
    lbl_Exit:
        Exit Sub
    End Sub
    The macro tries to emulate what I understood you wanted from the code you posted albeit with multiple words to check.
    If the message is empty (assuming no graphics in the signature if present) then the macro finds no listed word and skips to the end.
    If the message is empty and yhou add an attachment, the macro finds no listed word and skips to the end.
    If the macro finds a listed word and there is no attachment you get a warning.
    If the macro finds a listed word and there is an attachment it continuess to the end without a warning (apart from te temporary warning about finding a word).

    What is it that you want to happen that isn't happening?
    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
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Many thanks to you! The latest code works perfectly I tweaked the counter a bit as you have predicted and am quite happy.

    Would you be so kind, if you have time of course, to demonstrate how the code whiteout the counter would work? (“As it seems likely that you are looking for the presence of a particular attachment, then it would be better to test for that attachment (or an attachment of that type) rather than simply rely on a count.”)

    Best regards

  6. #6
    You still haven't explained what it is that you are really trying to do with regard to the attachments or how many required attachments there are, but for one named attachment
    You will need also to declare the variable bAttach as Boolean

        ' /* Check the required attachment(s). */
        For i = 1 To Item.Attachments.Count
            If Item.Attachments(i).fileName = "filename.ext" Then
                bAttach = True
                Exit For
            End If
        Next i
    
        If Not bAttach = True Then
            MsgBox "Attachment missing.", vbExclamation, "Message"
            Cancel = True
        End If
    or if the name varies, but the extension remains the same (here the last four characters of the filename are docx)
        ' /* Check the required attachment(s). */
        For i = 1 To Item.Attachments.Count
            If Right(Item.Attachments(i).fileName, 4) = "docx" Then
                bAttach = True
                Exit For
            End If
        Next i
    
        If Not bAttach = True Then
            MsgBox "Attachment missing.", vbExclamation, "Message"
            Cancel = True
        End If
    For more than one required attachment you will need to provide more detail.
    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
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Greetings,
    I’m honestly looking for an all-around macro that would help me will all the attachment types.
    Here is a flow chart of what I was thinking FIRST PHOTO

    Basically I would like Outlook to check if a new mail/reply includes the “Attached” field (SECOND PHOTO), regardless of file type or name. If it detects the keyword(s) but not an attachment it would display a warning otherwise it would precede as normal and sent the email.
    So far the counter works but I have come across problems with it and can’t trust it 100%.
    My images are attached. (hopefully)

    Best regards
    Attached Images Attached Images

  8. #8
    Unfortunately it doesn't work like that. It is not possible to readily distinguish attachments you add from elements in the message that are reflected as attachments - such as graphics in an html message. As your example shows the attachment is an image, separating it from graphics in the message presents a problem. If the attachments have a specific name (or names) then you can identify them from a list much like the keywords, but otherwise not. It may also be possible to check the size of the attachment - but if there is no consistency to the attachment it is always going to be hit and miss.

    There is more on the subject with example code at https://www.slipstick.com/outlook/em...tlook-message/
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Thought so
    Ok, for now I’m going to say thank you very much for all the help and explanation, and stick with the counter method for now.

    Best regards
    p.s thx for the link

Posting Permissions

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