Solved

Send email through Access using custom Outlook form

Posted on 2002-03-07
12
258 Views
Last Modified: 2007-12-19
Alright, here's the challenge.  I have a custom form that I created in Outlook to be able to send to someone.  I have been able to send the form using Outlook, but I need to automate the form.  So, I have an access database that has a similar form in which data is input.  I need to be able to, in code, populate the Outlook form and attach a file to the form.  I have been able to create a new mail message in code, but I haven't succeeded in accessing the custom form.  The form in question is in the 'Personal Forms Library' and is called 'GradReq'.
0
Comment
Question by:beachc
  • 6
  • 4
  • 2
12 Comments
 
LVL 9

Accepted Solution

by:
rjcpjc earned 300 total points
ID: 6848106
You need to set a reference to the Outlook Inbox folder and then use the following:

Set newItem = objFolder.Items.Add("IPM.Note.FormName")
newItem.UserProperties.Find("fieldname") = "Your Value Here"    'This syntax is for custom properties

newItem.Attachments.Add  "C:\My Documents\Q496.xls", _
    olByValue, 1, "4th Quarter 1996 Results Chart"

Hope that helps.
0
 

Author Comment

by:beachc
ID: 6848336
Actually I figured out a way to access the custom form:

    Set olns = ol.GetNamespace("MAPI")
    Set cf = olns.GetDefaultFolder(olFolderDrafts)

    ' Create a new Contact item
    Set c = ol.CreateItem(olMailItem)
   
    ' Specify which Outlook form to use:
    c.MessageClass = "IPM.Note.GradReq"

However the custom form isn't viewable when checking email over the web.  Do you know if there is some way to get around this so that the form structure is visible both using Outlook and the web?
0
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6848357
I think you're actually better off using the code I posted for stability's sake, but your syntax will definitely work.  What do you mean over the web?  Are you talking about Outlook Web Access or just sending the form between two users not on the same Exchange Server?
0
 

Author Comment

by:beachc
ID: 6848374
Our school has an outlook web access and when sending the form it appears as a standard email message, not as the custom form.
0
 

Author Comment

by:beachc
ID: 6848386
What code would need to preceed your suggestion because access doesn't seem to recognize 'objFolder.Items.Add()'?  Do I have to define objFolder somewhere or is that code designed to be run from the outlook end b/c I need to run from the Access end?
0
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6848405
  Set olns = ol.GetNamespace("MAPI")
   Set objFolder = olns.GetDefaultFolder(olFolderInbox)

Yes, you need to dim objFolder.

What version of Exchange?  That will make a difference on what you need to do for OWA forms to be visible.

But, you can't take an Outlook form and have it appear as both an Outlook custom form and an OWA custom form at the same time.  You'd have to have one or the other.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:beachc
ID: 6848440
I got your code to work, however I'm not sure what version of OWA we use.  Another small side point, do you know of any way to eliminate the annoying popup messages that ask if I'm sure I want Outlook to send the email on my behalf, etc...?
0
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6848466
You have a couple of options.

If you are in an Exchange environment, you can download and install the
Administrative options package.  This will allow you to configure the
security update.

If you are not, your best option would be to use a third party tool called
Redemption (www.dimastr.com/Redemption).

For more information abuot the security update, see :
http://www.slipstick.com/outlook/esecup.htm

About OWA, you need to know what version of Exchange.  But still, no matter what version you use, you cannot take an Outlook custom form and make it display in OWA and still display in Outlook at the same time.  They have to be two different forms.
0
 

Author Comment

by:beachc
ID: 6862245
Thank you very much for all your help
0
 

Expert Comment

by:eciabattari
ID: 8545520
beachc - i came accross your post and i'm trying to do the same; however, i'm having some problems and could use the help.  i have an access db that and a form that exports the data to a regular email message but i need to use a custom form.  can you assist? thanks

'------------------------------------------------------------------------------------------------------------------------------------------------------------
Public Sub SendEmailRequest()
On Error GoTo Err_MsgToContactWithErrorHandling

'This code references the Outlook Object Model to send an email message
   Dim strEmailAddress As String
   Dim strEmailAddressCC As String
   Dim strMessageSubject As String
   Dim strPurchaseID As String
   Dim strMessageText As String
   Dim objOutlook As New Outlook.Application
   Dim objMessage As MailItem
   
  If IsNull(Forms![frmInput]![ApprovedBy]) Then
      MsgBox "Your request cannot be sent, you need an Approver.  " _
      , vbOKOnly, "Cannot Send Request"
  Exit Sub
 
   Else
 
   'Create the email address
   If Forms![frmInput]![ApprovedBy] = "Somebody" Then
       strEmailAddress = "someone@somewhere.com"
   End If
   
  Debug.Print strEmailAddress
 
   'Create the PurchaseID
   Select Case Len(CStr(Forms![frmInput]![PurchaseID]))
       Case 1:    strPurchaseID = "0000" & CStr(Forms![frmInput]![PurchaseID])
       Case 2:    strPurchaseID = "000" & CStr(Forms![frmInput]![PurchaseID])
       Case 3:    strPurchaseID = "00" & CStr(Forms![frmInput]![PurchaseID])
       Case 4:    strPurchaseID = "0" & CStr(Forms![frmInput]![PurchaseID])
   End Select
  Debug.Print strPurchaseID
 
   'Create the message subject text
  strMessageSubject = "Pro-Card Approval Request - PurchaseID # " & strPurchaseID
  Debug.Print strMessageSubject
 
   'Create the message text
  strMessageText = "The following purchase is being requested. Please Approve or Reject by using the buttons above. " & Chr(13) & Chr(13)
  strMessageText = strMessageText & "Merchant Name: " & Forms![frmInput]![MerchantName] & Chr(13)
  strMessageText = strMessageText & "Transaction Date: " & Forms![frmInput]![TransDate] & Chr(13)
  strMessageText = strMessageText & "Amount: " & Forms![frmInput]![Amount] & Chr(13)
  strMessageText = strMessageText & "Purchased For: " & Forms![frmInput]![PurchasedFor] & Chr(13)
  strMessageText = strMessageText & "Description: " & Forms![frmInput]![Description] & Chr(13) & Chr(13)

 
   strMessageText = strMessageText & "This message may contain confidential information " & _
       "that is legally privileged and is intended only for the use of the parties to whom it " & _
       "is addressed. If you are not an intended recipient, you are hereby notified that any " & _
       "disclosure, copying, distribution or use of any information in this message is strictly " & _
       "prohibited. If you receive this message in error, please notify me immediately at the " & _
       "telephone number indicated above."
 
   Debug.Print strMessageText
 
   
  'Create the email message and send
  Set objMessage = objOutlook.CreateItem(olMailItem)
      With objMessage
          .To = strEmailAddress
          .CC = strEmailAddressCC
          .Subject = strMessageSubject
          .Body = strMessageText
          .VotingOptions = "Approve;Reject"
          .Send
      End With
 
   End If
 
Exit_MsgToContactWithErrorHandling:
   Exit Sub
   
Err_MsgToContactWithErrorHandling:
   Select Case Err
       Case 287
           strMsg = "If you want this to work, you " & _
               "must click Yes at the prompt. Do you " & _
               "want to try again?"
           intRes = MsgBox(strMsg, _
               vbYesNo + vbDefaultButton2, _
               "Outlook Security")
           If intRes = vbYes Then
               Resume
           Else
               GoTo Exit_MsgToContactWithErrorHandling
           End If
   End Select
           
End Sub
0
 

Author Comment

by:beachc
ID: 8545566
eciabattari,
Could you explain a bit more indepth about what exactly you're trying to accomplish?  Since it's been more than a year since I made this post I'm not even really sure what I used it for, because since then this project changed drastically making this part obsolete, I think.  However, if I can help in any way I would be glad to.
0
 

Expert Comment

by:eciabattari
ID: 8546103
I've created a database that will be used for purchaing.  When individuals input their request, theses a button called "Send Request" that invokes the above code.  The code creates an email using the data from the input form, and then sends that e-mail.  however,  my problem is that I want to use a custom for that has VB code running within it.  So, my problem is, how do I tell the database which form to use?  Can I place a form on a network drive and use it?  Thanks for the help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now