Email from excel with outlook..code win00 XL00
Posted by Dave on November 25, 2001 11:53 AM
How's everyone doing. I've pieced together some code to email a recipient if the name is in outlook contacts.In the body of the email I would like to show items needed from cells c1 to c6 (more cells will be added later all in column c) THE PROBLEM is that cells c1 to c6 will not be the same all the time there is an if condition attached such as =IF(A5<>0,"","need address information"). So sometimes there will be blank cells in the c column. If anyone can help me format the body of the email so not to include blanks and look nice and pretty I would greatly be thankful. I tried using a string and range but it proved difficult.
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim Fname As String
Dim string1 As String
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Recipient = Range("a11") 'email recipient name in cell
Fname = Left(Worksheets(1).Range("A11").Value, InStr(1, Worksheets(1).Range("A11").Value, " ") - 1)
Application.StatusBar = "Compiling email.....hang on!"
.To = Recipient
.CC = "firstname.lastname@example.org"
.Subject = "Items needed for valuation"
.Body = Fname & "," & Chr(13) & Chr(13) & _
"If possible could the folling items be supplied." & Chr(13) & Chr(13) & _
Range("c2") & vbCrLf _
& Range("c3") & vbCrLf _
& Range("c4") & vbCrLf _
& Range("c5") & vbCrLf _
& Range("c6") & vbCrLf & Chr(13) & Chr(13) & _
"Thank You," & Chr(13) & Chr(13) & _
.Attachments.Add ("c:\My Documents\book3.xls")
Application.StatusBar = False
Set objMail = Nothing
Set objOL = Nothing