Create an email from a custom form.

[Tina]

I have a custom form created in Excel, the form contains a number of fields for a user to input data. I need a button on the form that fires up Outlook and puts the values of these fields into the message area of a new email, the user would then need to be able to modify the email. Everything I have tried so far simply attaches the workbook as an attachment in an email - which I don't want, the data needs to be in the body of the email.
I then need a second button on the form that writes the data into a database.
Can you help?????

[Mark O'Brien]

Tina, quick answer to your question:

I believe that there is a way to make a worksheet the body of an email, however, I have yet to crack that one myself. You would need to have an understanding of CDO for this one.

Since I haven't been able to do this myself yet, I'd say that, right now, it can't be done.

I just don't want you banging you head against a wall on this one. I've spent a couple of days recently trying to crack this problem and it's a pain.

If you do get a solution, I'd be very interested to see it as well.

EDIT:: Oops, didn't read the second question, I got too excited. I can't really help you on the DB aspect but what kind of DB is it? Access, Oracle or simply an Excel spreadsheet? (someone will no doubt ask this)
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-01 06:44 ]

[dk]

Hi Tina,

Do you want the values from the textboxes in your email, or the worksheet? Your first problem can be solved by using something like this. You'll need to be a bit more specific if you can't get this to work. It assumes you have two textboxes called txtField1 and txtField2 and a command button called cmdOK. In the cmdOK click event use this:- YOU MUST SET A REFERENCE TO THE MICROSOFT OUTLOOK OBJECT LIBRARY IN TOOLS, REFERENCES!

Private Sub cmdOK_Click()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = "anyone@anywhere.com"
olMail.Subject = "Excel sent this!"
olMail.Body = Me.txtField1.Text
olMail.Body = olMail.Body & Chr(9) & Me.txtField2.Text

olMail.GetInspector.Activate

End Sub

Of course this is a very simple example but it should give you an idea. It simply adds txtField1's value, then a tab character and then txtField2's value.

If you need any further help or I've missed the point then please repost.

Regards,
Dan

Mark O'Brien

Since I haven't been able to do this myself yet, I'd say that, right now, it can't be done.

Is that a joke Mark?

[Mark O'Brien]

Is that a joke Mark?

Erm, yeah, OK.

No actually, I was reading the post slightly different from you. I was reading "send the worksheet" as the body of the email. When, in fact, more correctly, it was just the data that needed to sent as the body of the mail. (which is entirely possible)

[Qroozn]

Please use the attached macro and custom it to your needs. it send a message(not an attachment)


BoxTitle = ("EXCEL CONTENTS")
Message = "here is the excel info"

'Dimension variables.
Dim OL As Object, MailSendItem As Object

Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)

'Creates message
With MailSendItem
.Subject = "LEAD FEED BACK"
.Body = Message
.To = "test@test.com"
.Send
End With

'Ends Outlook session
Set OL = Nothing
MsgBox "Your correspondence has been posted", , "FEEDBACK STATUS"

End Sub