Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

I want to write code from Excel to Outlook eMail !! HELP !!

I want to write code. usually I know how to do it as an Excel user, I can turn on the recorder, look at the sample code and figure out where to go from there.
But i can't read out a cel (with a email addres) and write the conttest of this cel in vb as the To addres.

Pleas can anyone help this dutch(ie) boy.
  • 3
  • 2
1 Solution
On the Tools menu, click References.
In the References box, click to select the Microsoft Outlook 9.0 Object Library, and then click OK.
NOTE: If the Microsoft Outlook 9.0 Object Library does not appear in the Available References box, browse your hard disk for the file, Msoutl9.olb. If you cannot locate this file, you must run the Microsoft Outlook Setup program to install it before you proceed with this example.

Type the following procedure in the new module:
Sub sendMessage(Optional AttachmentPath)

   Dim olookApp As Outlook.Application
   Dim olookMsg As Outlook.MailItem
   Dim olookRecipient As Outlook.Recipient
   Dim olookAttach As Outlook.Attachment

   ' create the Outlook session.
   Set olookApp = CreateObject("Outlook.Application")

   ' create the message.
   Set olookMsg = olookApp.CreateItem(olMailItem)

   With olookMsg
      ' add the To recipient(s) to the message.
      Set olookRecipient = .Recipients.Add("Christopher Wyke")
      olookRecipient.Type = olTo

      ' add the CC recipient(s) to the message.
      Set olookRecipient = .Recipients.Add("Robert Dil")
      olookRecipient.Type = olCC

      ' set the Subject, Body, and Importance of the message.
      .Subject = "This is an Automation test with Microsoft Outlook"
      .Body = "Last test - I promise." & vbCrLf & vbCrLf
      .Importance = olImportanceHigh  'High importance

      ' add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
         Set olookAttach = .Attachments.Add(AttachmentPath)
      End If

      ' resolve each Recipient's name
      For Each olookRecipient In .Recipients
         If Not olookRecipient.Resolve Then
            olookMsg.Display   ' display any names that can't be resolved
         End If

      End With
      Set olookMsg = Nothing
      Set olookApp = Nothing
   End Sub

hmoedAuthor Commented:
Great, thanks, but......

how do i read out a cel from a excel sheet ?

????? = range("A1").value
Set olookRecipient = .Recipients.Add(" ?????  ")

how do i put this in one line.

one more time please and jou have my points,.... en more

Set olookRecipient = .Recipients.Add(sheets("Sheet1").range("A1").value)
hmoedAuthor Commented:
Your a friend DocM.
Thanks hmoed

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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