i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

Modify the Ranges and Values to suit your situation.

Dim OutApp As Outlook.Application
ThisWorkbook.Activate
Dim OutMail As Outlook.MailItem
Dim strbody As String

A = Sheets("Macros").Range("C16").Value
B = Sheets("Macros").Range("C18").Value
C = Sheets("Macros").Range("A1").Value

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Attached is the " & C & " CSFB Activity Report." & vbNewLine
& vbNewLine & _
"Please let me know if you have any questions."
Enclosures = wbk1

IncludeAttachments = True
With OutMail
.To = A
.CC = B
.BCC = ""
.Subject = "CSFB Activity Comparison"
.Body = strbody
.Attachments.Add Workbooks(wbk1).FullName
' .Attachments.Add Workbooks(wbk2).FullName
.Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
--
Best wishes,

Jim


"sdvl" wrote:

i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

I too would like to get a result from this; however i get a compile error:
user- defined type not defined.

Any suggestions?

"Jim Jackson" wrote:

Modify the Ranges and Values to suit your situation.

Dim OutApp As Outlook.Application
ThisWorkbook.Activate
Dim OutMail As Outlook.MailItem
Dim strbody As String

A = Sheets("Macros").Range("C16").Value
B = Sheets("Macros").Range("C18").Value
C = Sheets("Macros").Range("A1").Value

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Attached is the " & C & " CSFB Activity Report." & vbNewLine
& vbNewLine & _
"Please let me know if you have any questions."
Enclosures = wbk1

IncludeAttachments = True
With OutMail
.To = A
.CC = B
.BCC = ""
.Subject = "CSFB Activity Comparison"
.Body = strbody
.Attachments.Add Workbooks(wbk1).FullName
' .Attachments.Add Workbooks(wbk2).FullName
.Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
--
Best wishes,

Jim


"sdvl" wrote:

i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

Select your project in the VBE
tools|References|
look for Microsoft Outlook xx.x Object Library

and check it.

Nippy wrote:

I too would like to get a result from this; however i get a compile error:
user- defined type not defined.

Any suggestions?

"Jim Jackson" wrote:

Modify the Ranges and Values to suit your situation.

Dim OutApp As Outlook.Application
ThisWorkbook.Activate
Dim OutMail As Outlook.MailItem
Dim strbody As String

A = Sheets("Macros").Range("C16").Value
B = Sheets("Macros").Range("C18").Value
C = Sheets("Macros").Range("A1").Value

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Attached is the " & C & " CSFB Activity Report." & vbNewLine
& vbNewLine & _
"Please let me know if you have any questions."
Enclosures = wbk1

IncludeAttachments = True
With OutMail
.To = A
.CC = B
.BCC = ""
.Subject = "CSFB Activity Comparison"
.Body = strbody
.Attachments.Add Workbooks(wbk1).FullName
' .Attachments.Add Workbooks(wbk2).FullName
.Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
--
Best wishes,

Jim


"sdvl" wrote:

i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

--

Dave Peterson

Thank you, this is now working a treat.

Ideally i would like to start the body of the email with a line from the
workbook such as

Dear "name"

and then finish off with a standard signature from Outlook,

I could add the standard signature into the code but i don't get the nice
html effects i have in my signature

Any suggestions?


"Dave Peterson" wrote:

Select your project in the VBE
tools|References|
look for Microsoft Outlook xx.x Object Library

and check it.

Nippy wrote:

I too would like to get a result from this; however i get a compile error:
user- defined type not defined.

Any suggestions?

"Jim Jackson" wrote:

Modify the Ranges and Values to suit your situation.

Dim OutApp As Outlook.Application
ThisWorkbook.Activate
Dim OutMail As Outlook.MailItem
Dim strbody As String

A = Sheets("Macros").Range("C16").Value
B = Sheets("Macros").Range("C18").Value
C = Sheets("Macros").Range("A1").Value

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Attached is the " & C & " CSFB Activity Report." & vbNewLine
& vbNewLine & _
"Please let me know if you have any questions."
Enclosures = wbk1

IncludeAttachments = True
With OutMail
.To = A
.CC = B
.BCC = ""
.Subject = "CSFB Activity Comparison"
.Body = strbody
.Attachments.Add Workbooks(wbk1).FullName
' .Attachments.Add Workbooks(wbk2).FullName
.Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
--
Best wishes,

Jim


"sdvl" wrote:

i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

--

Dave Peterson

I don't. I don't use Outlook.

I'd go to Ron de Bruin's page and look through some of his sample email code.
http://www.rondebruin.nl/sendmail.htm

Nippy wrote:

Thank you, this is now working a treat.

Ideally i would like to start the body of the email with a line from the
workbook such as

Dear "name"

and then finish off with a standard signature from Outlook,

I could add the standard signature into the code but i don't get the nice
html effects i have in my signature

Any suggestions?

"Dave Peterson" wrote:

Select your project in the VBE
tools|References|
look for Microsoft Outlook xx.x Object Library

and check it.

Nippy wrote:

I too would like to get a result from this; however i get a compile error:
user- defined type not defined.

Any suggestions?

"Jim Jackson" wrote:

Modify the Ranges and Values to suit your situation.

Dim OutApp As Outlook.Application
ThisWorkbook.Activate
Dim OutMail As Outlook.MailItem
Dim strbody As String

A = Sheets("Macros").Range("C16").Value
B = Sheets("Macros").Range("C18").Value
C = Sheets("Macros").Range("A1").Value

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Attached is the " & C & " CSFB Activity Report." & vbNewLine
& vbNewLine & _
"Please let me know if you have any questions."
Enclosures = wbk1

IncludeAttachments = True
With OutMail
.To = A
.CC = B
.BCC = ""
.Subject = "CSFB Activity Comparison"
.Body = strbody
.Attachments.Add Workbooks(wbk1).FullName
' .Attachments.Add Workbooks(wbk2).FullName
.Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
--
Best wishes,

Jim


"sdvl" wrote:

i'm trying autosend e-mails from excel, but need to every time outoolk opens
already shows subject and message body. The hyperlink function is part of a
formula that provides acees to outlook

--

Dave Peterson


--

Dave Peterson