Consulting

Results 1 to 4 of 4

Thread: VBA - Message Input Box Before Sent Disappeared

  1. #1

    VBA - Message Input Box Before Sent Disappeared

    Hi,

    I'm new to VBA and I've tried to create code that before sending an e-mail with an attachment, a message box appears asking if the attachment has been checked before sending.

    This worked on the day it was implemented, but after restarting my PC, the code is still visible in ThisOutlookSession but does not run.

    Is there something I need to do in order for this to run each day?

    I also want to advance this to only run this if the attachment/s are specific attachment types, not sure if this is possible or not.

    Any help is appreciated.



    Code below:

    'ThisOutlookSession
    Option Explicit
    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
      Dim msg As String
      Dim att As Outlook.Attachment
        
      If TypeOf Item Is Outlook.MailItem Then
        If Item.Attachments.Count > 0 Then
          msg = "Have you double checked the attachment/s to ensure that the data is relevant?." & vbNewLine & _
                "If no, please press the No button and re-check." & vbNewLine
          For Each att In Item.Attachments
            msg = msg & vbNewLine & "* " & att.FileName
          Next
          If MsgBox(msg, vbQuestion + vbSystemModal + vbYesNo) = vbNo Then Cancel = True
        End If
      End If
    End Sub

  2. #2
    Have you used debugging to see if the code is running but the If is failing?

    VBA: How to Debug Code - Overview, Tools, Shortcut Keys (corporatefinanceinstitute.com)

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,187
    Location
    Just guessing here, so test before you intend to use in your outlook program

    Sub SendMail()
        Dim OutlookApp As Outlook.Application
        Dim OutlookMail As Outlook.MailItem
        Set OutlookApp = New Outlook.Application
        Set OutlookMail = OutlookApp.CreateItem(olMailItem)
        With OutlookMail
            .To = "address@domain.com"
            .CC = ""
            .BCC = ""
            .Subject = "File for your perusal"
            .BodyFormat = olFormatHTML
            .HTMLBody = "Hi, <p> I'm sending this message from Excel using VBA.</p>Please _
            find file attached."
            .Attachments.Add ActiveWorkbook.FullName
            .Importance = olImportanceHigh
            .ReadReceiptRequested = True
            If .Attachments.Count >0 then
                If Msgbox” Is the attachment relevant to the person receiving this email?, + vbYesNo + vbQuestion, “Test for Validity”) = Yes Then
                   .Send
                Else
                    Exit sub
               End if
          End if
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Thank you both for your replies, it's appreciated!

    It's strange because it seems to be working without any issues today, so not quite sure why yesterday was different. I'll monitor it and I'll take the advice given if it is inconsistent with when it works.

    Thank you!

Posting Permissions

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