Sendmail in Excel

  • Thread starter Thread starter ukmalcolm
  • Start date Start date
U

ukmalcolm

Hi,

My problem is a s follows.

I have a form on which a user will enter information into
a cell and then an email address into another cell. The
theory is that he can then press a send button and that
worksheet will be emailed as an attachment. However, the
recipient in the To: filed will always be variable.

Basically, we need to be able to open the Outlook Dialog
box with the workbook / worksheet attached with the cc
field already populated. All the user then has to do is
complete the To: filed and send.

I can bring up a dialog box with the attachment where the
To: and cc fileds are blank and I can send the attachment
direct to the email address entered by the user - but this
does not bring up a dialog box and goes directly to them.

I've tried all sorts and am wondering if this is actually
possible given that Outlook will send email where there is
an email address in the cc filed and nothing in the To:
field - I have tried using showdialog etc.

Anyone ever tried to do this?

Any help greatly appreciated.

Regards
 
Try this with display instead of Send
For more examples see http://www.rondebruin.nl/sendmail.htm

Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = "(e-mail address removed)"
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'In Excel 97 use ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Back
Top