473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access Function for Outlook_From Form

New to functions. Found the outlook function in search to add tracking to email from Access to Outlook.
Figured syntax for field from form. Issue form can have anywhere from 1 record to multiple records up to nine.
for 1 to 9 duplicates last email addr if <9

Have tried using for next loop which semi works but somehow need to incorporate a counter in form built in counter field called Count
but I can not seem to add it to existing code without failing at that member.


Function fctnOutlook(Optional FromAddr, Optional Addr, Optional CC, Optional BCC, _
Optional Subject, Optional MessageText, Optional Categories, Optional AttachmentPath, Optional Vote As String = vbNullString, _
Optional Urgency As Byte = 1, Optional EditMessage As Boolean = True)


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient


'Create the Outlook Session
Set objOutlook = CreateObject("Outlook.Application")
'Create the message
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Add the Recipients to the message above
With objOutlookMsg

If Not IsMissing(FromAddr) Then
.SentOnBehalfOfName = FromAddr
End If

Dim stDocName As String
Dim stLinkCriteria As String
'Dim Count As long

stDocName = "Approver"

For i = 1 To 2
If Not IsMissing(Addr) Then
Set objOutlookRecip = .Recipients.Add(Addr)
objOutlookRecip.Type = olTo
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNext
Next i
DoCmd.GoToRecord , , acFirst


If Not IsMissing(CC) Then
Set objOutlookRecip = .Recipients.Add(CC)
objOutlookRecip.Type = olCC
End If

If Not IsMissing(BCC) Then
Set objOutlookRecip = .Recipients.Add(BCC)
objOutlookRecip.Type = olBCC
End If

If Not IsMissing(Subject) Then
.Subject = Subject
End If

If Not IsMissing(MessageText) Then
.Body = MessageText
End If

If Not IsMissing(Categories) Then
.Categories = Categories
End If

If Not IsMissing(AttachmentPath) Then
'Check file exists before attaching!
If Len(Dir(AttachmentPath)) > 0 Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
Else
MsgBox "Attachment not found.", vbExclamation
End If
End If

If IsNull(Vote) = False Then
.VotingOptions = Vote
End If

Select Case Urgency
Case 2
.Importance = olImportanceHigh
Case 0
.Importance = olImportanceLow
Case Else
.Importance = olImportanceNormal
End Select

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

If EditMessage Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing

End Function
Feb 1 '08 #1
0 1075

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
6
by: MS | last post by:
Access 97 here. I want a simple way to "lock" certain records on a form. Some records remain "live" until all data is available which happens over time. When all the fields are complete, I want...
42
by: lauren quantrell | last post by:
So many postings on not to use the treeview control, but nothing recently. Is it safe to swim there yet with Access 2000-Access 2003?
16
by: TD | last post by:
This is the code under a command button - Dim ctl As Control For Each ctl In Me.Controls If ctl.BackColor <> RGB(255, 255, 255) Then ctl.BackColor = RGB(255, 255, 255) End If Next ctl
1
by: gm | last post by:
Hi; I have written a database that tracks all the installation we have ever done. I have a small heating company. I have recently started keeping a directory of digital photographs of the...
4
by: Peter | last post by:
Access 2007 optionally opens forms as tabs. There is an Access Option that controls this - Access Options Current Database Document Window Options Tabbed Documents. But how, from VBA, can I...
10
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Thank you for that. It was very...
16
by: John | last post by:
I am looking for VBA code that will work with Access 2003 to enable dragging and dropping a file/folder name from Windows XP Explorer into an Access form's text box. This is a common functionality...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.