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

Posted on 2003-03-14
Medium Priority
Last Modified: 2012-05-04
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.
Question by:hmoed
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 8140265
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


Author Comment

ID: 8140453
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

Expert Comment

ID: 8140479

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

Author Comment

ID: 8140503
Your a friend DocM.

Accepted Solution

DocM earned 300 total points
ID: 8140525
Thanks hmoed

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question