e-mail address

[Klemen25]

Hello

I managed to slowly build a code for my file and in it I used
'Application.Dialogs(xlDialogSendMail).Show.' for sending the file
from excel.

I like this as already managed to make (find and adjust to be honest)
a code (I am total noob) for name of the file I would like to send
from Excel, and also I need the user that will use this code to be
able to write different comments in the body of the mail that will be
sent.

But is it possible to add email addresses (that will always be the
same in this 'DialogSendMail'?
So that To, and CC fields will be already filled out?

.... So far i use the following code to name the file, hide the sheet,
and than to send it.
Const PATH As String = "R:\.....
.SaveAs Filename:=PATH & _
.Sheets("ALM").Range("G2").Value & "" & ".xls"
End With
ActiveWindow.SelectedSheets.Visible = False
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close
End Sub

Any easy way?
I looked at http://www.rondebruin.nl/sendmail.htm but was unable to
find the right solution, although there are great examples.
Thanks for any help!

 

[dg.courbat]

Hello,
I use this, it's a mailing system with 1 worksheet "Original" for the
"letter" and an other (donnees) with all informations for the message
(email address, name, client address.....)
In your case, you have to replace the xx, yy and zz informations by
your parametres.

Private Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Public Sub SendMailCDO()
'**********************************************************************
' Publipostage de l'onglet "Original" comme pièce jointe
' les informations pour l'envoi sont dans onglet "donnees"
' GCT le 30.08.2006
'**********************************************************************
Dim Pos As Integer
Dim iMsg As Object
Dim iConf As Object
Dim wb As Workbook
Dim NomUser As String
'*** login name
NomUser = String(100, Chr$(0))
GetUserName NomUser, 100
login_util = Left$(NomUser, InStr(NomUser, Chr$(0)) - 1)
Application.ScreenUpdating = False
Pos = 3 'initialise le début de la lecture des lignes
Val_Pos = ActiveWorkbook.Sheets("donnees").Range("A" & Pos).Value
Rep_Appl = ActiveWorkbook.Path
While Val_Pos <> 0
'*** mise en place des données dans original
Sheets("Original").Select
Range("F8") = ActiveWorkbook.Sheets("donnees").Range("A" & Pos)
Range("F9") = ActiveWorkbook.Sheets("donnees").Range("B" & Pos)
Range("F10") = ActiveWorkbook.Sheets("donnees").Range("C" & Pos)
Range("F11") = ActiveWorkbook.Sheets("donnees").Range("D" & Pos)
Sheets("Original").Copy ' créé un classeur avec la
feuille "Original"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Rep_Appl & "\Original.xls"
ActiveWindow.Close
'*** Envoi par mail
Dest = ActiveWorkbook.Sheets("donnees").Range("E" & Pos).Value
Sujet = ActiveWorkbook.Sheets("donnees").Range("F2").Value
Attache = Rep_Appl & "\Original.xls"
Corps = ActiveWorkbook.Sheets("donnees").Range("G2").Value &
Chr(13)
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusing") = x
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") = "yyy.yy.yy.yy"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = zz
.Update
End With
With iMsg
Set .Configuration = iConf
.To = Dest
.CC = login_util & "@compagnyname.com" 'si nécessaire
.BCC = ""
.FROM = login_util & "@compagnyname.com"
.Subject = Sujet
.TextBody = Corps
.AddAttachment Attache
.Send
End With
Kill Attache
Set iMsg = Nothing
Set iConf = Nothing
Set wb = Nothing
Pos = Pos + 1
Val_Pos = ActiveWorkbook.Sheets("donnees").Range("A" & Pos).Value
Wend
Sheets("original").Select
Range("F8:F11").Select
Selection.ClearContents
Range("B2").Select
Sheets("donnees").Select
Application.ScreenUpdating = False
End Sub


!!! you must activate the Microsoft CDO librarys in the menu "Tools,
VBAProject references when you are in the Visual Basic Editor.

Bonne chance. DG

 

[Ron de Bruin]

Hi

Use the Outlook code examples from my site
And then use Display instead of Send in the code

See also the tips, link is on every example page.

 

[Klemen25]

OK
I'll give it another try.