N
nike
Hi,
I would like to send automated mails from Excel,
the basic stuff works fine, but I still need some help on details.
The following code will show you the function I call to create a mail.
I pass various data to this function.
The data gets passed on and inserted.
What I need is an option to move the sent mail
from the sent mail folder to another specified folder.
The approach I have coded doesn't work properly...
Apart from that I need some help on how I can force Outlook
to use the standard mail Template when creating a new mail...
My Outlook is set up to use a certain footer when creating a new mail,
but this setting doesn't get recognised when the mail is created with
vba...
Any help/input would be very appreciated.
Bye
Nike
Function SendMail(strRecip As String, strSub As String, strBod As
String, bolDel As Boolean) As String
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objSentFolder As Object
Dim objSentSubFolder As Object
Dim myNamespace As NameSpace
Dim obInboxFolder As Object
Dim mySentItems As Object
Set objOutlook = CreateObject("Outlook.Application")
Set myNamespace = objOutlook.GetNamespace("MAPI")
Set mySentItems = myNamespace.GetDefaultFolder(olFolderSentMail)
Set obInboxFolder = myNamespace.GetDefaultFolder(olFolderInbox)
On Error Resume Next
Set objSentFolder = obInboxFolder.Parent.folders("Reg Test")
If objSentFolder Is Nothing Then
Set objSentFolder = obInboxFolder.Parent.folders.Add("Reg Test")
End If
Set objSentSubFolder = objSentFolder.folders(Format(Date, "DDMMYY"))
If objSentSubFolder Is Nothing Then
Set objSentSubFolder = objSentFolder.folders.Add(Format(Date,
"DDMMYY"))
End If
On Error GoTo 0
Set objOutlookMsg = objOutlook.CreateItem(0) 'Create a mail item
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strRecip)
Set objOutlookRecip = .Recipients.Add("CC user")
.Recipients(.Recipients.Count).Type = olCC
If Not objOutlookRecip.Resolve Then
SendMail = strRecip
Else
'MsgBox .Recipients(.Recipients.Count).Name
'I need the first Name of the recipient, but how?
'name returns the whole name...
.Subject = "Urgent - ..."
.Body = " - This mail was generated automatically - "
.Importance = olImportanceHigh
.DeleteAfterSubmit = bolDel
.Send
If bolDel = False Then
mySentItems.Items(1).Move objSentSubFolder
End If
End If
End With
Set objOutlook = Nothing
Set objOutlookMsg = Nothing
End Function
I would like to send automated mails from Excel,
the basic stuff works fine, but I still need some help on details.
The following code will show you the function I call to create a mail.
I pass various data to this function.
The data gets passed on and inserted.
What I need is an option to move the sent mail
from the sent mail folder to another specified folder.
The approach I have coded doesn't work properly...
Apart from that I need some help on how I can force Outlook
to use the standard mail Template when creating a new mail...
My Outlook is set up to use a certain footer when creating a new mail,
but this setting doesn't get recognised when the mail is created with
vba...
Any help/input would be very appreciated.
Bye
Nike
Function SendMail(strRecip As String, strSub As String, strBod As
String, bolDel As Boolean) As String
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objSentFolder As Object
Dim objSentSubFolder As Object
Dim myNamespace As NameSpace
Dim obInboxFolder As Object
Dim mySentItems As Object
Set objOutlook = CreateObject("Outlook.Application")
Set myNamespace = objOutlook.GetNamespace("MAPI")
Set mySentItems = myNamespace.GetDefaultFolder(olFolderSentMail)
Set obInboxFolder = myNamespace.GetDefaultFolder(olFolderInbox)
On Error Resume Next
Set objSentFolder = obInboxFolder.Parent.folders("Reg Test")
If objSentFolder Is Nothing Then
Set objSentFolder = obInboxFolder.Parent.folders.Add("Reg Test")
End If
Set objSentSubFolder = objSentFolder.folders(Format(Date, "DDMMYY"))
If objSentSubFolder Is Nothing Then
Set objSentSubFolder = objSentFolder.folders.Add(Format(Date,
"DDMMYY"))
End If
On Error GoTo 0
Set objOutlookMsg = objOutlook.CreateItem(0) 'Create a mail item
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strRecip)
Set objOutlookRecip = .Recipients.Add("CC user")
.Recipients(.Recipients.Count).Type = olCC
If Not objOutlookRecip.Resolve Then
SendMail = strRecip
Else
'MsgBox .Recipients(.Recipients.Count).Name
'I need the first Name of the recipient, but how?
'name returns the whole name...
.Subject = "Urgent - ..."
.Body = " - This mail was generated automatically - "
.Importance = olImportanceHigh
.DeleteAfterSubmit = bolDel
.Send
If bolDel = False Then
mySentItems.Items(1).Move objSentSubFolder
End If
End If
End With
Set objOutlook = Nothing
Set objOutlookMsg = Nothing
End Function