Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Create an email from a custom form.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?????

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    Code:
    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?

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •