Solved

Replying to an Outlook email from Access 7

Posted on 2012-04-09
4
328 Views
Last Modified: 2012-04-12
I have been using Outlook automation to send Outlook email from an Access 7 database, which is essentially a contact and project manager. I also link to an Outlook InBox, import new email into the database. I then run a query which creates a new table in which each email has a unique identifier. I can then assign each email to a relevant project and related work group members.  So for each project,  I end up with a pretty decent record of email that I have sent and received, and can create new email without leaving the project screen. Everything works well except I don't have a way to reply to an email from within Access.  I am thinking of adding a "Reply" button which would work like the "Send Email" code included below, except that it would copy the contents of the current email into a new form, and add something like "On <DATE> you wrote"  and bring to the top of the form to create my new message. I would then use the "Send Email" button or something like it. Before trying to build this though, I am wondering if anyone has a better way, or can maybe point me to some existing code.

 


Private Sub Sendemail_Click()

   Dim stDocName As String
   Dim stLinkCriteria As String
If Not IsNull(FaceID) Then
   stDocName = "frmFaces"
   stLinkCriteria = "FaceID=" & Forms![frmMaster2]![frmTransactionSubform].Form![With Whom]
   
   DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End If
   
     DoCmd.OpenForm "frmFaces"
    Dim t As String
    t = Forms![frmFaces].EMAIL
    DoCmd.Close
    DoCmd.OpenForm "frmMaster2"
         
Dim MyOutlook As Outlook.Application
Dim MyEmail As Outlook.MailItem
 
Set MyOutlook = CreateObject("Outlook.application")
Set MyEmail = MyOutlook.CreateItem(olMailItem)

MyEmail.To = t
MyEmail.Subject = Forms![frmMaster2].Form![Headline]
MyEmail.body = Forms![frmMaster2]![frmTransactionSubform].Form![Notes] & vbCrLf & GetSignature("PR")
 
MyEmail.display

Dim Clip As String
Dim textonly As String

Clip = Forms![frmMaster2].Form![Headline] 'make the line shorter for coding purposes
textonly = InStr(Clip, "#")
Clip = Replace(Clip, "#", "") 'remove the #s
MyEmail.Subject = Mid(Clip, 1, textonly - 1)
   
End Sub

   Function GetSignature(strSignatureName As String) As String
    Const ForReading = 1
    Const TriStateTrue = -1
    Dim objFSO As Object, objFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(Environ("USERPROFILE") & "\Application Data\Microsoft\Signatures\" & strSignatureName & ".txt", ForReading, False, TriStateTrue)
    GetSignature = objFile.ReadAll
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
   

End Function
0
Comment
Question by:jnero
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37826317
Too much is involved to do something like this in Access, let alone Access 7... IMHO...
Why not just reply from within outlook and avoid re-inventing the wheel?

Let's see what other experts may think...
0
 

Author Comment

by:jnero
ID: 37827429
Thanks.  I am trying to maintain a continuous workflow, eliminating white space between applications, which can increase productivity and cut costs significantly, especially if I can extend to a work group. Part of the benefit is the streamlining the activity, another part is avoidance of the distractions that invariably come when you go into another application, such as dealing with new email that has arrived. Yes it is a matter of discipline and concentration, but, at least at the team level, it would be easier to automate the process than enforce work habits for each member. But you may be right that this may be beyond the scope of Access 7 -- which kind of sends me back to the drawing board.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37827857
As with anything...
If you put enough work into it, you can do anything you want...
How much trouble you are willing to go through is another story though...
;-)

That being said your approach seems like it would be the best technique...
<Before trying to build this though, I am wondering if anyone has a better way, or can maybe point me to some existing code.>

JeffCoachman
0
 

Author Closing Comment

by:jnero
ID: 37840352
It was good to see that someone else agreed with my general approach while providing a reality check that I may be overreaching with Access 7. This was the last peg in my application and with that, I will see where I hit the wall in the next rev, which will likely be built on something else, but probably not by me.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

739 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