Solved: Opening Outlook from Excel and sending to a mailing list

[mdmackillop]

Hi All,
I'm using the following code from Excel to create an email ("16" replaces a workbook reference). Two questions.
What do I have to add to open Outlook, making the email visible for final editing before sending?
I've used the name of a mailing list in .to = "Valuations" which doesn't work. Can this be made to work, or do I just have to add the recipients to the code?
Regards
Malcolm
[VBA]Sub eMailValuation()

Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim MySub As String
Dim MyText As String
Dim MyVal As String
Dim MySum As String

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook

MySub = "Kitchen Project - Valuation No. " & "16"
MyText = "Please find attached our Valuation No. " & "16" & _
" together with a list of included properties." & vbCr & vbCr & _
"Regards," & vbCr & vbCr & "Malcolm"
MyVal = "F:\Valuations\Val" & Format("16", "000") & ".mdi"
MySum = "F:\Valuations\Summary.mdi"

Wb.Save
With EmailItem
.Subject = MySub
.Body = MyText
.To = "Valuations"
.Importance = olImportanceNormal
'.Attachments.Add MyVal
'.Attachments.Add MySum
.Save
End With
Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub[/VBA]

[Killian]

Hi Malc,
EmailItem.Display will show the mail item
I'll have to check about referring to mailing lists tomorrow (Outllok at work, y'see)

[mdmackillop]

Thanks Killian

[Killian]

OK, I've had a test that works for me...
I think the best approach to adding a Distribution List by name is this. If your name doesn't resolve, we'll have to have a re-think...[VBA]With myMailItem
.Display
Set myRecipient = .Recipients.Add("TestDistList")
myRecipient.Type = olTo
myRecipient.Resolve
End With
[/VBA]

[mdmackillop]

Hi Killian,
I had a problem with the Type line so I deleted it (Practical Computing Rule 18)
The following works fine. Thanks for your help.
Regards
Malcolm
[VBA]
With EmailItem
.Subject = MySub
.Body = MyText
Set myRecipient = .Recipients.Add("TestDistList")
myRecipient.Resolve
.Importance = olImportanceNormal
'.Attachments.Add MyVal
'.Attachments.Add MySum
.Save
.Display
End With
[/VBA]

[brettdj]

I had a problem with the Type line so I deleted it (Practical Computing Rule 18]

Very droll