PDA

View Full Version : Solved: Excel VBA Email to more than one recipient



grohm
04-18-2007, 03:07 AM
Hi Guys!

In the code below everythings functions, except if i want to put mor than one recipient (with the array type). I'm sure its only a small thing....

Anyone knows what's wrong?



Sub Button2_Click()
Dim message As Object, OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
Dim AWS As String
'active sheet gets sent by email
AWS = ThisWorkbook.FullName
Set message= OutApp.CreateItem(0)
With message
.to = Array("xxx@xxx.com", "yyy@yyy.com")

.Subject = "A new engagement is ready for processing! " & Date & Time
.attachments.Add AWS
.Body = "Hi SWISSO. Please process this engagement."
'mail gets displayed
.Display
'mail goes into sent items
'Mail.Send
End With
'Outlook schliessen
OutApp.Quit
Set OutApp = Nothing
Set Nachricht = Nothing
End Sub

thanks to you.

Greetz

Ben

CCkfm2000
04-18-2007, 03:21 AM
try

Sub Button2_Click()
Dim message As Object, OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
Dim AWS As String
'active sheet gets sent by email
AWS = ThisWorkbook.FullName
Set message= OutApp.CreateItem(0)
With message
.to = Array("xxx@xxx.com; yyy@yyy.com")

.Subject = "A new engagement is ready for processing! " & Date & Time
.attachments.Add AWS
.Body = "Hi SWISSO. Please process this engagement."
'mail gets displayed
.Display
'mail goes into sent items
'Mail.Send
End With
'Outlook schliessen
OutApp.Quit
Set OutApp = Nothing
Set Nachricht = Nothing
End Sub

grohm
04-18-2007, 03:29 AM
hi CCkfm2000

that doesn't seem to work....:-(


it says that "outlook cannot translate your string"....

Charlize
04-18-2007, 04:58 AM
Try this one :
Sub Button2_Click()
Dim message As Object, OutApp As Object, wb As Workbook
Set OutApp = CreateObject("Outlook.Application")
Set wb = ActiveWorkbook
Set message = OutApp.CreateItem(0)
With message
'Could be that you have to alter ; to ,
.to = "xxx@xxx.com; yyy@yyy.com"
.Subject = "A new engagement is ready for processing! / " & _
Date & " - " & Time
.attachments.Add wb.FullName
.Body = "Hi SWISSO. Please process this engagement."
'mail gets displayed
.Display
'mail goes into sent items
'Mail.Send
End With
'Outlook schliessen
'OutApp.Quit
Set OutApp = Nothing
'Set Nachricht = Nothing
End SubCharlize

grohm
04-18-2007, 07:53 AM
worked now

sometimes, it's the little things :-)


thanks!


greetz

ben

grohm
04-20-2007, 04:10 AM
guys, i have another prob.

if the file gets filled out in excel and sent with the button with the code like above, the receiver only sees the file, but not with the values a user has put in. in other words: it looks like you have to save your entries, saving the file and the send it and then it's ok.

but is there a way that you dont need to save this, sending a excel form with entered data without the forcing to save?

grohm
04-23-2007, 01:57 AM
anyone?