Send Task from Access to multiple emails from listbox

[GeminiG]

Hi I'm fairly new to access so apologies for any newbie language.

I have a form (frmTasks) that has a listbox (lbAssignedTo) which contains multiple email addresses. I'm trying to figure out how to tell set the TOSet ToContact = Task.Recipients.Add) in the task email to the selections from the listbox. I'm sure this can be done but I haven't had any luck in finding any coding examples. The form is using a command button (cmdSend) and I'm sending the task notification using VBA (Set Task = Outlook.CreateItem(olTaskItem))

Any help or guidance is appreciated in advance.

 

[Micron]

Don't know how you're doing the email part. Also, I'm assuming what you need is a way to enumerate the listbox selected items rather than how to build a multi-valued recipient list. The request is not real clear. I also presume that you know whether or not you need to handle the possibility that this could be triggered by an event (such as a button click) when no listbox selections have been made. With all that in mind, perhaps

 Dim lngRow As Long

With Me.lbAssignedTo
  For lngRow = 0 To .ListCount - 1
     If .Selected(lngRow) Then
        do email stuff
     End If
  Next lngRow
 End With

 

[GeminiG]

This is what I currently have, where would I put your code?

Private Sub cmdSendTask_Click()
Dim outlookApp As Outlook.Application
Dim outlookTask As Outlook.TaskItem
Set outlookApp = CreateObject("outlook.application")
Set outlookTask = outlookApp.CreateItem(olTaskItem)
    
    strEmailRecipients = ""
      For n = 0 To Me!lbEmail.ListCount - 1
         If Me!lbEmail.Selected(n) = True Then
            strEmailRecipients = strEmailRecipients & "; " & Me!lbEmail.Column(2, n)
         End If
      Next n
      strEmailRecipients = Mid(strEmailRecipients, 3)
      
With oMsg
.To = ("strEmailRecipients")
End With


With outlookTask
.Subject = "Reminder for Task: " & Me.TaskName
.Body = "This is a reminder 3 days before due. Task name: " & Me.TaskName & " is due on " & Me.DueDate
.ReminderSet = True
.DueDate = Me.DueDate
'3 days prior reminder
.ReminderTime = Me.DueDate - 3 & " 8:00AM"
ReminderPlaySound = True
.Save


End With
    MsgBox "Task has been sent to Outlook successfully.", vbInformation, "Set Task Confirmed"
End Sub

 

[xenou]

Your code would go in the click event for your SendTask command button. But Micron's code isn't doing anything your code isn't already doing - so did you actually write this yourself and do you know what it's doing?

THis looks strange:

strEmailRecipients = Mid(strEmailRecipients, 3)

possibly that is a mistake of some kind.

 

[GeminiG]

No I really don't I'm using the other code because my list box has 2 columns. One for the contactID and one for the actual email address. Sorry like I said I'm just beginning and this is the most difficult problem I've come across. I'm not sure if the code is even in the correct order.

 

[Micron]

I agree it looks like you're close, but you need to be very specific about what happens when you run this.
strEmailRecipients = strEmailRecipients & "; " & Me!lbEmail.Column(2, n) will cause the list to start with ;[space]
The use of Mid here is to remove the leading semi colon and space, so it's actually OK.

- listbox column is zero based, so you are trying to get the value from the third column when you say you want #2. That could be your issue. Again, you need to be clear on what's happening (or not). Note my signature advice on this. To get column 2, row n use Me.lbEmail.Column(1, n)
I would not use the bang operator (!) with Me.
Edit: anytime you
- SET an object, you should destroy it (otherwise it continues to hog memory space until the app closes)
- open a recordset, you should close it (not created here, just adding it to this short list of good practices).

End With
    MsgBox "Task has been sent to Outlook successfully.", vbInformation, "Set Task Confirmed"
Set outlookApp = Nothing
Set outlookTask = Nothing
End Sub

What I'd do if it were my code is add an error handling routine, especially when working with Automation (interacting with other programs like Outlook). I would DEFINITELY turn on "Require variable declaration". I doubt you have it on because strEmailRecipients has not been declared (unless it is in the declarations section at the top of the module holding this code).

 

[GeminiG]

Thank you for your patience and tips Micron. I'm learning so much! I've made a few changes but now my error is a compile error that variable N is not defined. How do I define N?

Option Compare DatabaseOption Explicit


Private Sub cmdSendTask_Click()
Dim outlookApp As Outlook.Application
Dim outlookTask As Outlook.TaskItem
Dim strEmailRecipients As String


Set outlookApp = CreateObject("outlook.application")
Set outlookTask = outlookApp.CreateItem(olTaskItem)


    
    strEmailRecipients = ""
      For n = 0 To Me!lbEmail.ListCount - 1
         If Me!lbEmail.Selected(n) = True Then
            strEmailRecipients = strEmailRecipients & "; " & Me!lbEmail.Column(1, n)   'change 2 to column number containing the address
         End If
      Next n
      strEmailRecipients = Mid(strEmailRecipients, 3)
      
With oMsg
.To = ("strEmailRecipients")
End With


With outlookTask
.Subject = "Reminder for Task: " & Me.TaskName
.Body = "This is a reminder 3 days before due. Task name: " & Me.TaskName & " is due on " & Me.DueDate
.ReminderSet = True
.DueDate = Me.DueDate
'3 days prior reminder
.ReminderTime = Me.DueDate - 3 & " 8:00AM"
ReminderPlaySound = True
.Save


End With
    MsgBox "Task has been sent to Outlook successfully.", vbInformation, "Set Task Confirmed"
Set outlookApp = Nothing
Set outlookTask = Nothing
End Sub

Here is a link to the file: https://drive.google.com/file/d/0B7YELkEida5MdTJwNmxWZnhCemc/view?usp=sharing

 

[xenou]

The use of Mid here is to remove the leading semi colon and space, so it's actually OK.

Thanks. I had forgotten that the third argument to Mid can be omitted.

 

[Micron]

This may be a cut and paste error: Option Compare DatabaseOption Explicit
Should be
Option Compare Database
Option Explicit

Your error means that any variable must be declared/defined when you use Option Explicit. It is wise practice to turn this on by default (note that this does not propagate to code that has already been written). It helps avoid creating compile or run time bugs that happen when you think you are affecting a data type but it is some other data thype. Usually Access figures out what you want an undeclared variable to be, but when it doesn't, the bug can be difficult to find.

Here is a source for good practices when naming objects.
MS Access Naming Conventions - Access World Forums

and one for words NOT to use
Microsoft Access tips: Problem names and reserved words in Access

A good source to help decide on what data types are available to assign to variables
Microsoft Access tips: Data Types in Access

And my own personal note:
Dim Dept, User, Agent, Unit As String - does NOT make each one a string variable. The first 3 are variants because they are not declared.
This is how to multi declare on one line
Dim Dept As String, Agent As String, Unit As String
although using my own style of naming, which pretty much guarantees you will never use a reserved word, it would be
Dim strDept As String, strAgent As String, strUser As String

Hope that all helps.