Advanced Sample: Email Is Required Before Saving Invoice

Our invoicing app exposes all its major features via COM add-in objects. This enables VBA macros to call its functionalities, making it programmable with the VBA scripting language.

Note: This sample requires the "Enterprise" edition. For more information, see Differences between Editions.

In Macro-Enabled Invoicing Template (c5101), we looked at how to make your template a macro-enabled template and provided information and resource links involved, such as Office and Excel (Microsoft 365) security settings, digital signatures, etc. In Invoiced Total in English Words (c5102), we provided a practical example implemented as a macro-enabled template, i.e., a user-defined Excel formula that is able to convert a numeric value into English words.

First, you need to obtain the COM add-in object. This could be done using VBA code like this:

Public Sub CallImfe(ByVal sCmd As String)
    On Error Resume Next
    Dim oComAddin As Office.COMAddIn
    Set oComAddin = Application.COMAddIns("Imfe.Connect")
    If oComAddin Is Nothing Then
        MsgBox "Invoice Manager for Excel is not installed!", vbOKOnly
        Return
    End If
    oComAddin.Object.UisClickProc (sCmd)
End Sub

This CallImfe procedure takes one argument, i.e., the command that you want to call. This is the button name that you want to call. For example, if you want to call the "Save To DB" command, you simply pass in the button name "oknCmdSave".

The CallImfe procedure obtains the Invoice Manager COM add-in by looking into the "Application.COMAddIns" collection. If it cannot find "Imfe.Connect" (This is the "ProgID" of Invoice Manager), it shows an error message. Otherwise, it passes the command to the add-in object.

The commands are always executed against the active workbook and active worksheet. Since different worksheets could have buttons with the same name, your VBA macro code should make sure the right workbook and worksheet are activated before issuing a command. For example, the "Quote" and "Purchase Order" worksheets in the default template all have the button named "oknCmdSave".

Once you have the CallImfe procedure ready, it is easy to call it. In this sample, we add macro code to check if the email field in the "Bill To" section is blank. If so, it fails, and a message is shown to ask the user to enter the email address. Feel free to see the sample at email invoice (c3017).

1. First, open your template.
2. Press ALT+F11 on the keyboard to open the macro editor (the Visual Basic for Applications editor).
3. Create a new module by clicking the menu "Insert", and then "Module".
4. Copy and paste the following code into the editor.

   Public Sub NewSaveToDBProc()
    
       If Range("oknWhoEmail").Value = "" Then
           MsgBox "Please enter email address in the 'Bill To' section.", vbExclamation + vbOKOnly, "Save Invoice"
           Exit Sub
       End If
       
       CallImfe ("oknCmdSave")
    
   End Sub
   

5. Click "File" and then "Save".
6. Click "File" and then "Close and Return to Microsoft Excel".
7. Click the "Design Mode" button.
8. Right-click the "Save To DB" button and choose "Assign Macro..." from the shortcut menu.
9. On the "Assign Macro" dialog box, click and choose "NewSaveToDBProc" (which is the procedure name we created in the previous steps).
10. Click "OK" to assign this new macro to the button and close the "Assign Macro" dialog box.
11. Rename the button from the original "oknCmdSave" to something else, for example "MyNewSaveToDB". To do this, make sure the button is selected, and then enter the new name into the name box on the formula bar.
12. Exit design mode.
13. Save the template.

You can provide any button name as the argument provided to the "CallImfe" procedure. To find out the name of a button, first switch your template into design mode, and then click the button. The name box on the left side of the formula bar shows the button name.