Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2022, 12:23 PM
bmiller bmiller is offline Create Macro that sends an email when a check box is marked Windows 10 Create Macro that sends an email when a check box is marked Office 2019
Novice
Create Macro that sends an email when a check box is marked
 
Join Date: Jan 2022
Posts: 1
bmiller is on a distinguished road
Default Create Macro that sends an email when a check box is marked

Hello All!

I have created a table that has a list of tasks with due dates and a check box from the developer tab that indicates the task is complete.

I would like to program a macro that automatically sends me an email when the check box is marked as complete, so that am notified and can go look over it. Is this possible?
Reply With Quote
  #2  
Old 01-04-2022, 10:48 PM
gmayor's Avatar
gmayor gmayor is offline Create Macro that sends an email when a check box is marked Windows 10 Create Macro that sends an email when a check box is marked Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,138
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The document would have to be saved as macro enabled and contain all the code. This creates its own problems as you cannot force a user to enable or run the macros. However ...

Assuming an ActiveX check box, associate the following code with the checkbox -
Code:
Option Explicit

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        ActiveDocument.Save
        Return_As_Mail_Attachment
    End If
End Sub

The following in a new module will send the current document as an e-mail attachment. NOTE THE COMMENTS!:


Code:
Option Explicit

Sub Return_As_Mail_Attachment()
'Graham Mayor - https://www.gmayor.com - Last updated - 05 Jan 2022
'Send the document as an attachment _
  in an Outlook Email message

'IMPORTANT *******
'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
'*****************

Const sAddress As String = "someone@somewhere.com"    '"your email address"
Const strMessage As String = "This is the message text" & vbCr & _
      "Completed document is attached"

Dim OlApp As Object
Dim olInsp As Object
Dim oItem As Object
Dim oDoc As Document, eMailDoc As Document
Dim oRng As Range
Dim sPath As String
Dim sName As String
    Set oDoc = ActiveDocument
    On Error GoTo err_Handler:
    sPath = oDoc.FullName
    sName = oDoc.Name
    oDoc.Close 0
    'Get Outlook if it's running
    Set OlApp = OutlookApp()
    'Create a new mailitem
    Set oItem = OlApp.CreateItem(0)

    With oItem
        .to = sAddress
        .Subject = sName & " completed"
        .attachments.Add sPath
        Set olInsp = .GetInspector
        Set eMailDoc = olInsp.WordEditor
        Set oRng = eMailDoc.Range
        oRng.Collapse 1
        .Display
        oRng.Text = strMessage & vbCr
        .send
    End With

lbl_Exit:
    Set oItem = Nothing
    Set OlApp = Nothing
    Set olInsp = Nothing
    Set oRng = Nothing
    Set oRng = Nothing
    Exit Sub
err_Handler:
    Err.Clear
    GoTo lbl_Exit
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Email sends mailmerge file behind email rather than attachment TLC1974 Mail Merge 2 07-22-2016 12:53 AM
Marked Link/element send per email s8884 OneNote 0 01-18-2016 09:47 AM
Outlook macro to check a value of a cell in an attachment and send an email based on that value ketangarg86 Outlook 13 03-25-2015 07:11 AM
Create Macro that sends an email when a check box is marked Help with macro not working - Pull from fields > attaches doc from folder > sends email mikey386 Excel Programming 2 01-08-2015 12:20 AM
Spell check issue - correctly spelled words marked misspelled lak08 PowerPoint 0 10-26-2012 06:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft