Sending Mail via VBA

[Aschrum]

Hi,

I want to send a mail including content from an excel file.
Therefor the first step has to be sending a mail through Outlook 2007 only.

So far my code is the following and doesn't work yet.

Public Function BegleitungenKWsenden()
Dim app As Application 'Wrong or not necessary?
Dim itm As CreateItem 'Wrong or not necessary?
Set app = CreateObject("Application")
Set itm = Application.CreateItem(0) 'Shows a failure
On Error Resume Next
AppActivate ("Outlook")
Err.Clear
With itm
.To = ""
.Subject = ""
.Body = ""
.GetInspector
.Display
.Send
End With

On Error GoTo 0

Set app = Nothing
Set itm = Nothing

End Function

I've created it as a module. Should it maybe be a class module?

André

[skatonni]

Try this (in a regular module in Excel)


Public Function BegleitungenKWsenden()
Dim app As Object
Dim itm As Object

Set app = CreateObject("Outlook.Application")
Set itm = app.CreateItem(0)

With itm
.To = "You"
.Subject = "Test"
.Body = "Hello"
.Display
'.Send
End With

Set app = Nothing
Set itm = Nothing

End Function

[Aschrum]

Actually I'd say I've already tried it in your way. Nevermind, that step works now. Thank you.

How can I adress the mail to several persons in multiple lines?

.to "a@b.c; d@e.f"

works

.to "a@b.c; _
d@e.f"

doesn't.

The following goes closer to my actual point.

1. How can I create a bulleted list in the body? Is it possible to do it in VBA or will I have to write it as <ul> in html?
Macro recorder in word brings up this


With ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = ChrW(61623)
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleBullet
.NumberPosition = CentimetersToPoints(0.63)
.Alignment = wdListLevelAlignLeft
.TextPosition = CentimetersToPoints(1.27)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With

ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""
Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior

Could this be transcribed to Outlook VBA?

2. How can I catch data from a unique Excel file?

Dim xWB As Excel.Workbook
Dim xRange As Excel.Range
xRange("C16:C44").Select

This is what I've got so far, but obviously incomplete, since the link to an Excel file is missing.

André