The following code activates outlook to send an e-mail useing an email
adress
in the cell of the excell spreadsheet. The problem is that outlook
keeps poping up
a warning that other programs are trying to access my address book. I
know that
this warning can be turned off in outlook express. Anyone know how to
turn the
warning off in outlook or make the routine run useing outlook express?

Thanks in advance!!




Sub PrintPDF_auto()

'This routine automatically sends an e-mail from excell...
'it does so by accessing Microsoft Outlook directly.
'the below needs to be set up on the computer for it to work....
'you 'll need to add a reference to the Outlook Object Library.
'From Excel 's Visual Basic editor you'd goto
'Tools - References and choose Microsoft Outlook Object Library
'From VB you'd goto
'Project - References and choose Microsoft Outlook Object Library

' This sub automatically selects the printer friendly quote sheet,
changes the
' printer to PDF mailer, and prints the quote

Sheets("Quote print sheet").PrintOut , , 1, , "maxx PDFMAILER
Standard"

Dim myAttachments As Outlook.Attachments
Dim objOutlook As Outlook.Application
Dim objEMailMsg As Outlook.MailItem
Dim Record
Dim Sht
Dim Attachis
Dim Subjectis
Dim Bodyis


Set objOutlook = New Outlook.Application
Set objEMailMsg = objOutlook.CreateItem(olMailItem)
Set myAttachments = objEMailMsg.Attachments

'use column 12
'the below gets the e-mail address out of ONE cell..
'the (1,2) 1 stands for the row number and the 12 stands for column 12
'Sht = Worksheets("Quote print sheet").Cells(1, 12)
'Set myrecipient = .Recipients.Add(Sht)
'myrecipient.Type = olTo



'I think the below counts how many things are in columb N
'i.e. the variable Record ends up being set to 3,4 etc..
Record = Application.CountA(Sheets("Quote print sheet").Range("N:N")) +
1
With objEMailMsg

'the below is the start of a loop
x = 1
Do
Sht = Worksheets("Quote print sheet").Cells(x, 14)

Set myrecipient = .Recipients.Add(Sht)
myrecipient.Type = olTo
x = x + 1
Loop Until x = Record
'the above is the end of the loop

'get the subject line info from row 3 column 12.....
Subjectis = Worksheets("Quote print sheet").Cells(3, 12)
..Subject = (Subjectis)

'the below is hardcoded way to set the subject line and does work....
'.Subject = "testing the sendfinal by bob."

'get the body message information from row 4 column 12....
Bodyis = Worksheets("Quote print sheet").Cells(4, 12)
..htmlbody = (Bodyis)

'the below is hardcoded way to set the body of the message and does
work....
'.htmlbody = "find the temp.pdf attached...."
..Display
'.send

'myAttachments.Add "C:\Documents and Settings\terry\Desktop\email.xls"
'the above does not work......

'get attachment location from row 2 column 12
Attachis = Worksheets("Quote print sheet").Cells(2, 12)
myAttachments.Add (Attachis)

'the below is hardcoded way to add the attachment and does work...
'myAttachments.Add ("C:\a_pdf_mail\temp.pdf")

'myAttachments.Add ("C:\a_pdf_mail\temp.pdf")
'myAttachments.Add.send "C:\a_pdf_mail\temp.pdf"
End With
End Sub


--
Bob Quinn
------------------------------------------------------------------------
Bob Quinn's Profile: http://www.excelforum.com/member.php...o&userid=24052
View this thread: http://www.excelforum.com/showthread...hreadid=376593

You cannot automate Outlook Express. It does not expose an object model but
see the following as possible solutions...

Using Outlook Express to email......
John Walkenbach http://www.j-walk.com/ss/excel/tips/tip86.htm

Using CDO to avoid Security warnings in Outlook.....
Ron De Bruin http://www.rondebruin.nl/cdo.htm

--
Cheers
Nigel



"Bob Quinn" wrote
in message ...

The following code activates outlook to send an e-mail useing an email
adress
in the cell of the excell spreadsheet. The problem is that outlook
keeps poping up
a warning that other programs are trying to access my address book. I
know that
this warning can be turned off in outlook express. Anyone know how to
turn the
warning off in outlook or make the routine run useing outlook express?

Thanks in advance!!




Sub PrintPDF_auto()

'This routine automatically sends an e-mail from excell...
'it does so by accessing Microsoft Outlook directly.
'the below needs to be set up on the computer for it to work....
'you 'll need to add a reference to the Outlook Object Library.
'From Excel 's Visual Basic editor you'd goto
'Tools - References and choose Microsoft Outlook Object Library
'From VB you'd goto
'Project - References and choose Microsoft Outlook Object Library

' This sub automatically selects the printer friendly quote sheet,
changes the
' printer to PDF mailer, and prints the quote

Sheets("Quote print sheet").PrintOut , , 1, , "maxx PDFMAILER
Standard"

Dim myAttachments As Outlook.Attachments
Dim objOutlook As Outlook.Application
Dim objEMailMsg As Outlook.MailItem
Dim Record
Dim Sht
Dim Attachis
Dim Subjectis
Dim Bodyis


Set objOutlook = New Outlook.Application
Set objEMailMsg = objOutlook.CreateItem(olMailItem)
Set myAttachments = objEMailMsg.Attachments

'use column 12
'the below gets the e-mail address out of ONE cell..
'the (1,2) 1 stands for the row number and the 12 stands for column 12
'Sht = Worksheets("Quote print sheet").Cells(1, 12)
'Set myrecipient = .Recipients.Add(Sht)
'myrecipient.Type = olTo



'I think the below counts how many things are in columb N
'i.e. the variable Record ends up being set to 3,4 etc..
Record = Application.CountA(Sheets("Quote print sheet").Range("N:N")) +
1
With objEMailMsg

'the below is the start of a loop
x = 1
Do
Sht = Worksheets("Quote print sheet").Cells(x, 14)

Set myrecipient = .Recipients.Add(Sht)
myrecipient.Type = olTo
x = x + 1
Loop Until x = Record
'the above is the end of the loop

'get the subject line info from row 3 column 12.....
Subjectis = Worksheets("Quote print sheet").Cells(3, 12)
Subject = (Subjectis)

'the below is hardcoded way to set the subject line and does work....
'.Subject = "testing the sendfinal by bob."

'get the body message information from row 4 column 12....
Bodyis = Worksheets("Quote print sheet").Cells(4, 12)
htmlbody = (Bodyis)

'the below is hardcoded way to set the body of the message and does
work....
'.htmlbody = "find the temp.pdf attached...."
Display
'.send

'myAttachments.Add "C:\Documents and Settings\terry\Desktop\email.xls"
'the above does not work......

'get attachment location from row 2 column 12
Attachis = Worksheets("Quote print sheet").Cells(2, 12)
myAttachments.Add (Attachis)

'the below is hardcoded way to add the attachment and does work...
'myAttachments.Add ("C:\a_pdf_mail\temp.pdf")

'myAttachments.Add ("C:\a_pdf_mail\temp.pdf")
'myAttachments.Add.send "C:\a_pdf_mail\temp.pdf"
End With
End Sub


--
Bob Quinn
------------------------------------------------------------------------
Bob Quinn's Profile:
http://www.excelforum.com/member.php...o&userid=24052
View this thread: http://www.excelforum.com/showthread...hreadid=376593