Email the form

[angelapercival]

Hi all,

I have created a form in Excel which is generate by using a macro recorded against a 'button'. It doesn't work great but does the job I need.

Is there any way to grab all the info in the spread sheet and pre prepare it for emailing? Or have a 'Email Submit' button within Excel?

Or if anyone can think of any better options. To explain what I am trying to achieve.

Users can select to complete one of many forms, depending on the option the user selects they are given a list of fields to complete i.e. Enquiry Form they are given a list of fields to complete related to enquiries.

This form is to be completed an emailed.

Thanks

 

[Charlotte E.]

If your e-mail client is Outlook, you can use this:
http://www.EXCELGAARD.dk/Lib/Outlook/

Otherwise, you can use this:
http://www.EXCELGAARD.dk/Lib/CDO/


CE

 

[angelapercival]

Thanks, I have worked out how to send as an attachment. But what I am now looking to do is sending the data in the excel spreadsheet pasted in the email. Here is what I have: (The error is with the body - copying the ranges)

Hope you can helps. Cheers.

Sub CreateEmail()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(olmailitem)

For Each ToRecipient In Array("(e-mail address removed)")
OlMail.Recipients.Add (ToRecipient)
Next ToRecipient

'fill in Subject field- in this case whatever value is in cell A1
OlMail.Subject = Range("B20").Value

'fill in Body field -
OlMail.Body = "Hi," & vbNewLine & vbNewLine & "Please find attached a completed " & Range("B20:C31").Value


'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it.

End Sub