Sending word document to multiple email addresses from a list box
I am fairly new with VBA. I have created a document which, when completed, allows the user to click on a button and email the whole document to me. That works fine.
I want to modify it and am getting lost.
What I want is:
A list box which has the names of four different people in it.
The user clicks on one, two, three, or all four of those listed names to highlight them. If they click on a name a second time, it unhighlights that name.
Once the user has selected the person or persons they wanted, they will click the button, and it will send a copy of the document to the email address of each person highlighted in the list box.
Here is the code I have used for my single button, and as I said, this works fine:
Private Sub CommandButton1_Click()
Dim oStory As Range
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
ActiveDocument.Fields.Update
ActiveDocument.SaveAs FileName:="PERMISSIONS REQUEST.doc"
MsgBox "Thank you. Your Permissions Request was sent to your IT Dept", vbOKOnly, "Thank you. Your Permissions Request was sent to your IT Dept"
'This macro requires the Outlook Object library to be checked
'in the vba editor Tools > References
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
On Error Resume Next
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End If
'see if Outlook is running and if so turn your attention there
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then 'Outlook isn't running
'So fire it up
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
'Open a new e-mail message
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem 'and add the detail to it
.To = "stezan@sstuwa.org.au" 'send to this address
.Subject = "Permissions Request Form" 'This is the message subject
.Body = "Thank you. Your IT Department will review your form and contact you if there are any questions." ' This is the message body text
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
.Send
'**********************************
End With
If bStarted Then 'If the macro started Outlook, stop it again.
oOutlookApp.Quit
End If
'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
End Sub
-------------------------
Can anyone help me to write coding to do what I need it to do with the list box names?
Thank you.
Zane
Two steps 1. In the
Two steps
1. In the properties for the listbox set the Multiselect property to Multi rather than single.
2. To interrogate for selected items a routine like the following will do.
Sub ReturnLBSelections()
Dim N As Long
With Sheet1.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) Then
MsgBox N
End If
Next N
End With
End Sub
The msgbox line obviously needs to be amended to compile a string of email recipients.
Thank you. I think I
Thank you. I think I understand, I'll give that a try.
Bit more info just in case
Bit more info just in case :-). Paste the following into a spare module and make the neccessary address corrections. The Addressline variable can then be used in the To. section of your email send routine.
Option Explicit
Global AddressLine As String 'Global so it can be passed between subs
Sub ReturnLBSelections()
Dim N As Long
AddressLine = "" 'Reset the global variable
With Sheet3.ListBox1 'Needs to be redirected to your listbox
For N = 0 To .ListCount - 1
If .Selected(N) Then
'Concatenate email addresses from the worksheet data into a single string seperated by semi colons
AddressLine = AddressLine & Sheet3.Range("A" & N + 1).Value & ";"
End If
Next N
End With
AddressLine = Left(AddressLine, Len(AddressLine) - 1) ' Remove the last semi colon
MsgBox AddressLine
End Sub
additional question
Thank you.
In your coding you have a line:
With Sheet3.ListBox1
Is that in a Word doc? I thought Sheet was used in Excel docs. Or am I misunderstanding?
Zane
No you are correct.. The
No you are correct.. The worksheet holds the email addresses for the listbox.. Since you're running the macro from Excel I thought it would be ok... I'm not familiar enough with Word - sorry.