Replying to an Outlook email from Access 7

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
jneroAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
jneroAuthor Commented:
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
 
jneroAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.