cmd button, send form via email

  • Thread starter Thread starter andy
  • Start date Start date
A

andy

I have a protected Exel form which works very nicely. We
have put it on our intranet, and would like to have a
command button at the bottom of the form to send the
filled out form to a specific email address. I don't know
if it is a series of steps such as programatically doing a
file save as.. and opening a email putting a subject line
in the Re: and sending, or if it is as easy as
incorporating a mailto: (e-mail address removed). I'm just
guessing! It would be nice to display a dialog box at the
end that says "Form has been submitted, to save a copy
locally you must use the File, Save As... command" and an
OK button.

Could someone please send me a code snippet, which I image
would be put in the command button click event.

Thank you in advance
God bless you
 
Andy, you cannot send the form directly but can either copy the form values
from the form, or if they are stored in a worksheet, from the worksheet.
These can be written into the body of an email or the whole workbook
attached to an email.

The following provides a template that you might wish to adapt. The .body
shows how to get values from the worksheet, but these could also be the
values from the form controls. The Attachment.Add can be also used as
shown, use the olByValue to attach a copy of the workbook or olByReference
if you would like it to be a link. The .To (add CC, BCC) address can be
formed from any string or values from a userform or worksheet cell, thus you
can adapt the email recipient accordingly. HTH.

In the VBA editor use the Tools->References option to select the Microsoft
Outlook Library

Sub MailSomeData()
'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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "A title "
.Body = "This is an automated email to advise that the data is
attached to this email." & _
"First data value:" &
Worksheets.("Sheet1").Range("A1").Value & vbcrlf & _
"Next data value:" &
Worksheets.("Sheet1").Range("A2").Value
.Attachments.Add (C:\workbook.xls"), olByValue
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Cheers
Nigel
 
Nigel,

Thank you for answering the post. I would like to place
the Excel ActiveWorkbook in the email as an attachment. I
looked at some old posts and they took me to
http://www.rondebruin.nl/cdo.htm however I could not get
it to work for me.

Does the file need to be written (SaveCopyAs) to a temp
file? or can the ActiveWorkbook just be sent (or do I
loose the changes the user made on the form if I don't use
a SaveCopyAs?

If you have some code I can try to send Outlook email
with Excel Workbook as attachment, please post here.

Thank you in advance.
God bless you
Andy
 
Andy, Try the following (assume you are using outlook) and that you have set
a reference to outlook in the VB editor tools->references area.
The delete after submit removes the entry from the senders email outbox
after the file is sent.

Sub MailWorkbook()
'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 = "(e-mail address removed)"
.Subject = "Supplier Report for "
.Body = "This is an automated email to advise that the report is
attached to this email. "
.Attachments.Add ActiveWorkbook.FullName
.DeleteAfterSubmit = True
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Back
Top