Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Creating a new Outlook e-mail from Excel that contains data from the Excel spreadsheet

What I want to do is use a CommandButton to make it so that when a user clicks on the button (which is in Excel2000), an e-mail message in Outlook 2000 is created that contains data from the Excel spreadsheet and also the Path of the Excel workbook. Below is what I have been using per Microsoft's KnowledgeBase (http://msdn.microsoft.com/library/officedev/off2000/olconWorkingWithFormsInVisualBasicEditor.htm), but it doesn't seem to work correctly, any suggestions?

This code is within Sheet1

Private Sub CommandButton1_Click()
    Dim myMail As Outlook.MailItem
    Set myMail = Application.CreateItem(olMailItem)
    With myMail
        .To = "email@domain.com"
        .Subject = "Subject"
        .Body = "This cell contains: " & Sheet1.Range("G18").Value & "The path to the workbook is: " & ActiveSheet.PageSetup.CenterFooter = ThisWorkbook.FullName
    End With
End Sub

I seem to get an error at the point that says "myMail As Outlook.MailItem" and I'm wondering if this is an incorrect Object name or something. Maybe I need to declare this as the Compile error message that I get is "User-defined type not defined".

I appreciate any insight that anyone may provide.
0
Szrachen
Asked:
Szrachen
  • 4
  • 3
1 Solution
 
jrspanoCommented:
try this
you have to have a application obj first and namespace object


   Sub NewMailMessage()
    Dim ol As New Outlook.Application
    Dim ns As Outlook.NameSpace
    Dim newMail As Outlook.MailItem

    'Return a reference to the MAPI layer.
    Set ns = ol.GetNamespace("MAPI")

    'Create a new mail message item.
    Set newMail = ol.CreateItem(olMailItem)
    With newMail
        'Add the subject of the mail message.
        .Subject = "Training Information for October 1997"
        'Create some body text.
        .Body = "Here is the training information you requested:" & vbCrLf

        'Add a recipient and test to make sure that the
        'address is valid using the Resolve method.
        With .Recipients.Add("mindym@imginc.com")
            .Type = olTo
            If Not .Resolve Then
                MsgBox "Unable to resolve address.", vbInformation
                Exit Sub
            End If
        End With

        'Attach a file as a link with an icon.
        With .Attachments.Add _
            ("\\Training\training.xls", olByReference)
            .DisplayName = "Training info"
        End With

        'Send the mail message.
        .Send
    End With

    'Release memory.
    Set ol = Nothing
    Set ns = Nothing
    Set newMail = Nothing
End Sub

0
 
SzrachenAuthor Commented:
I tried using your code and changing it to suit my needs. Here is the current code right now and I still get an error on the "ol As New Outlook.Application" part that says, "User-defined type not defined."
Here is the code:

Private Sub CommandButton2_Click()
   Dim ol As New Outlook.Application
   Dim ns As Outlook.NameSpace
   Dim newMail As Outlook.MailItem

   'Return a reference to the MAPI layer.
   Set ns = ol.GetNamespace("MAPI")

   'Create a new mail message item.
   Set newMail = ol.CreateItem(olMailItem)
   With newMail
       'Add the subject of the mail message.
       .Subject = "Worksheet"
       'Create some body text.
       .Body = "Here is the link to the :" & ThisWorkbook.FullName

       'Add a recipient and test to make sure that the
       'address is valid using the Resolve method.
       With .Recipients.Add("e-mail@e-mail.com")
           .Type = olTo
           If Not .Resolve Then
               MsgBox "Unable to resolve address.", vbInformation
               Exit Sub
           End If
       End With

       'Send the mail message.
       .Send
   End With

   'Release memory.
   Set ol = Nothing
   Set ns = Nothing
   Set newMail = Nothing
End Sub

Thanks again for your help.
0
 
jrspanoCommented:
this might be a stupid question but... you do have outlook installed on your development machine don't you?  also have you gone to tools/references and referenced microsoft outlook 9.0 object library?  this would cause that also
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SzrachenAuthor Commented:
Not a stupid question at all. Yeah, I have it installed. The Outlook 9.0 object library was the solution to my problem. It now works correctly. Thank you for your help!
0
 
SzrachenAuthor Commented:
Thanks for your help!
0
 
jrspanoCommented:
No problem! glad I could help.
0
 
SzrachenAuthor Commented:
Is it also possible to Underline text in the Body section of the e-mail or format the information in the Body section somehow?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now