Linking directly to an access form from an email

**stardust1979** · 09-30-2014, 10:27 PM

Hi,
I have a form which on selecting a command button sends out an email to a manager to say a specification is ready to be reviewed (this contains a link to a sharepoint site where the spec resides). The "approval" details are stored in by DB also so I would like to have included in that email a link back to the access db and the particular record so the person receiving the email can easily approve, if that makes sense.
Is that possible? Does anyone know how?
Here is my code for generating the email (at the point "To approve please click here" is where I would like my link to go):
[i][i] Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
"Dear xxxx, " & _
"There is a new Development Specification ready for your approval : " & _

txtWorkRequestID & " - " & txtDescription & " " & _
"Click on this link to open the file : " & _
"<a href='" & txtSpecLink & "'>" & _
""">Link to the file</A>" & _
"To approve please click here : " & _
" Kind Regards," & _
" xxxx"
vRecipientList = DLookup("Email", "tblStaff", "[MALTTeam]= 'MAN'")
vSubject = "New xxxx Development Specification ready for your approval"

On Error Resume Next
With OutMail
.to = vRecipientList
.CC = ""
.BCC = ""
.Subject = vSubject
.HTMLBody = strbody
.Display 'or use .Send
End With
Thanks in advance

**ranman256** · 10-01-2014, 06:16 AM

Remember to add the OUTLOOK OBJECTS in the vbe menu

Code:

Public Sub SendEmail()
Dim sBody as string
Dim vTo vSubj 

vSubj = "Teste" & " " & rst![SAP] & " " & rst![Nome]
sBody = "your body here"
  
Send1Email vTo, vSubj, sBody
End Sub
 
'----------------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark OUTLOOK OBJECTS in the vbE menu, Tools, References
'-------------
Public Function Send1Email (ByVal pvTo, ByVal pvSubj, ByVal pvBody, optional pvFile ) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody
    if not isMissing(pvFile) then .Attachments.Add pvFile, olByValue, 1
    
   .Send
End With
EmailO = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function
ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function

**tonez90** · 10-01-2014, 05:00 PM

Here is another slightly more complex one that you could try out.

Public Function SendEMessage(varTo As Variant, StrSubject As String, StrBody As String, _
bolAutoSend As Boolean, bolSaveInOutbox As Boolean, bolAddSignature As Boolean, _
Optional varCC As Variant, Optional varBCC As Variant, Optional varReplyTo As Variant, _
Optional varAttachmentPath As Variant, Optional varImagePath As Variant, _
Optional varHtmlFooter As Variant, Optional DeleteMessage As Boolean = True) As Boolean
'================================================= ================
'
'varto: a string of email addresses, multiples delimted by semi-colon
'strSubject: subject line for the email
'strBody: body of the email, must be wrapped in <html> </html> tags, and optionally any other formatting tags
'bolAutoSend: determines whether email is sent automatically or displayed to the user
'bolSaveInOutbox: determines if the message is saved in the outbox
'boladdsignature: determines if the user's default signature is added to the outgoing email
'varCC: (Optional) CC email addresses, multiples delimited by semi-colon
'varBCC: (Optional) BCC email addresses, multiples delimited by semi-colon
'varReplyTo (Optional) If specified sets the reply to email address, multiples delimited by semi-colon
'varAttachmentPath: (Optional) If specified attaches the file
'varImagePath: (Optional) If specified embeds the image in the body of the email
'varHtmlFooter: (Optional) If specifed, inserts an html file as a footer to the message.
'ASSUMPTIONS: Outlook, HTML message format, Word is the default editor
'When performing some of the optional steps the message is constructed in the following order
'signature then embedded image then footer then body text, so the actual email would read as follows
'body text, footer, embedded image, signature
'================================================= ================
On Error GoTo HandleError

Dim I As Integer
Dim strtempfile As String
Dim strMsg As String
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objInsp As Outlook.inspector
Dim objWord As Word.Application
Dim objdoc As Word.Document
Dim objrange As Word.Range

SendEMessage = False

Set gappOutLook = New Outlook.Application 'Create the Outlook session.
Set objOutlookMsg = gappOutLook.CreateItem(olMailItem) 'Create the message.

StrBody = RemoveBlankLines(StrBody) 'ReplaceCRLFwithSPACE(strBody) 'Replace any vbcrlf with 

With objOutlookMsg

If Not IsMissing(varTo) Then
If varTo <> "" And Not IsNull(varTo) Then
For I = 1 To CountCSWords(varTo)
Set objOutlookRecip = .Recipients.Add(GetCSWord(varTo, I)) 'Add the TO recipient(s) to the message.
objOutlookRecip.Type = olTo
Next I
End If
End If

If Not IsMissing(varCC) Then
If varCC <> "" And Not IsNull(varCC) Then
For I = 1 To CountCSWords(varCC)
Set objOutlookRecip = .Recipients.Add(GetCSWord(varCC, I)) 'Add the cc recipient(s) to the message.
objOutlookRecip.Type = olCC
Next I
End If
End If

If Not IsMissing(varBCC) Then
If varBCC <> "" And Not IsNull(varBCC) Then
For I = 1 To CountCSWords(varBCC)
Set objOutlookRecip = .Recipients.Add(GetCSWord(varBCC, I)) 'Add the bcc recipient(s) to the message.
objOutlookRecip.Type = olBCC
Next I
End If
End If

If Not IsMissing(varReplyTo) Then
If varReplyTo <> "" And Not IsNull(varReplyTo) Then
For I = 1 To CountCSWords(varReplyTo)
Set objOutlookRecip = .ReplyRecipients.Add(GetCSWord(varReplyTo, I)) 'Add the bcc recipient(s) to the message.
Next I
End If
End If

If (Not IsMissing(varAttachmentPath)) Then 'if attachment is specified
If (varAttachmentPath <> "") And (Not IsNull(varAttachmentPath)) Then 'check it is valid
If Dir(varAttachmentPath, vbHidden + vbSystem + vbReadOnly + vbDirectory) <> "" Then 'check the file actually exists
Set objOutlookAttach = .Attachments.Add(CStr(varAttachmentPath)) 'Add attachments to the message.
End If
End If
End If

.Subject = StrSubject 'Set the Subject of the message.

.BodyFormat = olFormatHTML 'set format to html

If bolAddSignature Or Not IsMissing(varImagePath) Or Not IsMissing(varHtmlFooter) Then 'if signature or embedded image or html footer
Set objInsp = objOutlookMsg.GetInspector 'this causes the default signature to be added to the message
Set objdoc = objInsp.WordEditor
If objdoc Is Nothing Then
strMsg = "Outlook must use Word as the email editor. Follow these instructions to fix the problem." & vbCrLf & vbCrLf & _
"Tools>Options" & vbCrLf & "Then select 'Mail Format' tab" & vbCrLf & "Ensure Use Microsoft Office Word 2003 to edit e-mail messages."
MsgBox strMsg
objOutlookMsg.Close olDiscard
GoTo SendEMessage_Done
End If

Set objWord = objdoc.Application

If bolAddSignature = False Then 'If the user had a signature it would have been applied, if we dont want it then we need to delete it here
objdoc.Range.Delete
End If

objdoc.SpellingChecked = True 'doesnt matter for autosend, but helps the user if the message is being displayed
Else
.HTMLBody = StrBody
End If

If bolSaveInOutbox = False Then 'if message not to be saved after sending
.DeleteAfterSubmit = True 'specify that it should be deleted
End If

If (bolAutoSend = True) And (.Recipients.Count > 0) Then 'check that there is at least 1 recipient before trying to autosend
If DeleteMessage = True Then .DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.Send
' Call emailaudit(Now(), atCNames(1), UBound(varTo), StrSubject, StrBody, IIf(UBound(varAttachmentPath) > 0, varAttachmentPath, ""))
Else
Err = 0
On Error Resume Next
.Display 'Attempt to display the message
If Err <> 0 Then 'if the mail cound not be displayed then display a warning and discard the message
MsgBox "It was not possible to display the message, check that there are no dialog boxes open in Outlook." & vbCrLf & "Please close all Outlook windows and emails, and then attempt this update again.", vbCritical
.Close olDiscard
GoTo SendEMessage_Done
End If
End If
End With

SendEMessage = True

SendEMessage_Done:
'need to clean up everything
Set gappOutLook = Nothing
Set objOutlookMsg = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
Set objInsp = Nothing
Set objWord = Nothing
Set objdoc = Nothing
Set objrange = Nothing
Exit Function
HandleError:
Call manage_error("Mod_Mail", "SendEMessage", Err.number, Err.Description, True) 'This is a user written error capture for my databases - change it to msgbox or whatever
Resume SendEMessage_Done
End Function

Linking directly to an access form from an email

Thread Tools

Linking directly to an access form from an email

Similar Threads

Web fill-in on a web page to send info directly to an access database table?

Access database won't open directly

Access 2007: Linking tables via ODBC will lose some fields unless linking as trusted

Why 2 users cant open an access file directly???

SENDING EMAIL MESSAGES DIRECTLY FROM ACCESS

Posting Permissions