Outlook

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
598
Office Version
  1. 365
HI I know this for outlook,

if any body can help me on this,

have this code in outlook to open combobox and it work when select the template nothing happens and sometimes get error run time error 432 and highlight this line: Set oMail = Application.CreateItemFromTemplate(strTemplate)

here it's the code:
Code:
Public Sub ChooseTemplate()


'Dim oMail As Outlook.MailItem
'im oContact As Outlook.ContactItem


'If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
' Set oContact = ActiveExplorer.Selection.Item(1)


Dim strTemplate As String
   UserForm1.Show


    Select Case lstNum
    Case -1
'  -1 is what you want to use if nothing is selected
         strTemplate = "Requesting Payment"
    Case 0
         strTemplate = "C.C. Authorization2"
    Case 1
        strTemplate = "Payment Inquiry-Current_Pastdue2"
    Case 2
         strTemplate = "Payment Inquiry-Current_Pastdue_Aging."
    Case 3
         strTemplate = "Provide C.C. Information_2.oft"
    Case 4
         strTemplate = "Refund Approval"
    Case 5
         strTemplate = "Contra Approval"
    Case 6
         strTemplate = "Terminate Due to Non Payment"
    Case 7
         strTemplate = "Remittance Address"


    End Select


    strTemplate = "C:\Documents and Settings\rossj1\Application Data\Microsoft\Templates\" & strTemplate & ".oft"
    Set oMail = Application.CreateItemFromTemplate(strTemplate)


'With oMail
'  .To = oContact.Email1Address
' .ReadReceiptRequested = True
'  .Subject = "My Macro test"
 ' .Body = "Hi " & oContact.FirstName & "," & vbCrLf & vbCrLf & oMail.Body
 ' .Display
'End With
 ' End If
Set oMail = Nothing


End Sub


thank you
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
Re: Outoook

Okay, let's try it this way. I'd like to you copy/paste the following code so that it's exactly as you see it. I've tested it, and it seems to work fine. So make sure you've selected a contact, run it, and then click the OK button on the userform. You should get a few messages along the way. Please post those messages here exactly as you see them. Also, if there are any errors, please specify which line is getting the error, and the exact error number and message as well. (Note: If the code runs as expected, you can delete those lines in red.)

Regular Module:

Code:
Option Explicit

Public lstNum As Integer

Public Sub ChooseTemplate()

    Dim oMail As Outlook.MailItem
    Dim oContact As Outlook.ContactItem
    Dim strTemplate As String
    
    If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
    
        Set oContact = ActiveExplorer.Selection.Item(1)
        
        UserForm1.Show
        
        Select Case lstNum
            Case -1, 0 'no selection or first selection
                 strTemplate = "Requesting Payment"
            Case 1
                 strTemplate = "C.C. Authorization2"
            Case 2
                strTemplate = "Payment Inquiry-Current_Pastdue2"
            Case 3
                 strTemplate = "Payment Inquiry-Current_Pastdue_Aging"
            Case 4
                 strTemplate = "Provide C.C. Information_2"
            Case 5
                 strTemplate = "Refund Approval"
            Case 6
                 strTemplate = "Contra Approval"
            Case 7
                 strTemplate = "Terminate Due to Non Payment"
            Case 8
                 strTemplate = "Remittance Address"
        End Select
        
        [COLOR=#ff0000]MsgBox "Your index number for Select Case is " & lstNum[/COLOR]
        
        strTemplate = "C:\Documents and Settings\rossj1\Application Data\Microsoft\Templates\" & strTemplate & ".oft"
        
        [COLOR=#ff0000]MsgBox "The path and name for your .oft file is  " & strTemplate[/COLOR]
        
        [COLOR=#ff0000]If Len(Dir(strTemplate, vbNormal)) = 0 Then
            MsgBox strTemplate & " does not exist"
        Else
            MsgBox strTemplate & " does exist"
        End If[/COLOR]
        
        Set oMail = Application.CreateItemFromTemplate(strTemplate)
        
        With oMail
            .To = oContact.Email1Address
            .ReadReceiptRequested = True
            .Subject = "My Macro test"
            .Body = "Hi " & oContact.FirstName & "," & vbCrLf & vbCrLf & oMail.Body
            .Display
        End With
        
    End If
    
    Set oMail = Nothing
    Set oContact = Nothing
    
End Sub

UserForm Module:

Code:
Option Explicit

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "Payment Required"
        .AddItem "Credit CardAuthorization"
        .AddItem "Past Due Amount"
        .AddItem "Suspend Account"
        .AddItem "Provide CC Information"
        .AddItem "Refund Approval"
        .AddItem "Contra Approval"
        .AddItem "Terminate Account"
        .AddItem "Remittance Address"
    End With
End Sub

Private Sub btnOK_Click()
    lstNum = ComboBox1.ListIndex
    [COLOR=#ff0000]With Me.ComboBox1
        MsgBox "Index number = " & lstNum & "; Value = " & .Value
    End With[/COLOR]
    Unload Me
End Sub
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
598
Office Version
  1. 365
Re: Outoook

thank you.

i posted but when i clicked the macro nothing happens, one question you say make sure you select a contact, don't understand, because what i want is click select the template and open it and then select or type contact once the template is open.

thank you,
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
Re: Outoook

Here's what your code does...

1) It checks whether you've selected a contact from your Contracts folder in Outlook.

2) If no contact is selected, the procedure ends, and so nothing happens.

3) If a contact is selected, the userform is shown, and allows you to make a selection from the combobox.

4) Once the OK button is pressed, the userform is unloaded, an email is created based on the chosen template (determined by the selection made in the combobox and Select Case statement), and then the email is displayed.

Isn't this what you want?
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
598
Office Version
  1. 365
Re: Outoook

No.

because i have all these templates, i just want to be able to select the template from the list and when click OK to open the template for me and the will add the email address or contact.

thank you
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726

ADVERTISEMENT

Re: Outoook

...and the will add the email address or contact.

Are you saying that you will manually add the email address? And what about the ReadReceiptRequested, Subject, and Body? Leave them so that the macro enters them or are you going to enter them manually as well?
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
598
Office Version
  1. 365
Re: Outoook

Yes, I will enter them manually I just to select the template to use, cause subject and it's different every time.

Thanks so much for your help.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726

ADVERTISEMENT

Re: Outoook

Then you'll need to remove the If/End If statement, which tests for a selected contact, and you can get rid of the With/End With statement, as follows...

Code:
Public lstNum As Integer

Public Sub ChooseTemplate()

    Dim oMail As Outlook.MailItem
    Dim strTemplate As String
    
    UserForm1.Show
    
    Select Case lstNum
        Case -1, 0 'no selection or first selection
             strTemplate = "Requesting Payment"
        Case 1
             strTemplate = "C.C. Authorization2"
        Case 2
            strTemplate = "Payment Inquiry-Current_Pastdue2"
        Case 3
             strTemplate = "Payment Inquiry-Current_Pastdue_Aging"
        Case 4
             strTemplate = "Provide C.C. Information_2"
        Case 5
             strTemplate = "Refund Approval"
        Case 6
             strTemplate = "Contra Approval"
        Case 7
             strTemplate = "Terminate Due to Non Payment"
        Case 8
             strTemplate = "Remittance Address"
    End Select
    
    strTemplate = "C:\Documents and Settings\rossj1\Application Data\Microsoft\Templates\" & strTemplate & ".oft"
    
    Set oMail = Application.CreateItemFromTemplate(strTemplate)
    
    oMail.Display
    
    Set oMail = Nothing
    
End Sub

Note that there's no need to change your original code for the userform.
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
598
Office Version
  1. 365
Re: Outoook

Thank you very Much for all your help, Works Fantastic.
 

Forum statistics

Threads
1,141,715
Messages
5,708,052
Members
421,541
Latest member
Akidev

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top