Link to home
Start Free TrialLog in
Avatar of hmoed
hmoed

asked on

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.
Avatar of DocM
DocM

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
         olookRecipient.Resolve
         If Not olookRecipient.Resolve Then
            olookMsg.Display   ' display any names that can't be resolved
         End If
      Next
         .Send

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

Avatar of hmoed

ASKER

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)
Avatar of hmoed

ASKER

Your a friend DocM.
ASKER CERTIFIED SOLUTION
Avatar of DocM
DocM

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial