In VBA, you'll need to include the "Microsoft Outlook 14.0 Object Library
"(under Tools->References)
then, this VBA code should work:(use your own email addresses for
"name@domain")---------------------------Option Explicit
Sub Test_Mail()
Dim Stat, fso
Dim Mail_Addr_To, Mail_Addr_CC, Mail_Subject, Mail_Body, FileName
Set fso = CreateObject("Scripting.FileSystemObject")
'------------------------------------------------------------
' Note: the list of email addresses must be separated by (;)
'------------------------------------------------------------
Mail_Addr_To = "name@domain;name@domain"
Mail_Addr_CC = "name@domain"
Mail_Subject = "Test Email"
Mail_Body = "This is a test email from Outlook"
FileName = "C:\temp\TD_Drawings.txt"
If (fso.fileexists(FileName)) Then
Stat = Send_Mail(Mail_Addr_To, Mail_Addr_CC, Mail_Subject, Mail_Body,
FileName)
End If
End Sub
Function Send_Mail(TO_Addr, CC_Addr, MsgSubject, MsgBody, MsgAttachment)
Dim I As Long, fso
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim Stat
Application.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Err.Clear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Err.Clear
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
'--------------------------------------------
' If Outlook is not running, this starts it
'--------------------------------------------
If Err.Number <> 0 Then
Stat = Shell("outlook.exe ", vbNormalNoFocus)
Application.Wait (Now + TimeValue("0:00:04"))
Err.Clear
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Exit Function
End If
End If
On Error GoTo 0
'--------------------------------------------
With olMail
.To = TO_Addr
If (CC_Addr & "X" <> "X") Then _
.CC = CC_Addr
.Subject = MsgSubject
.body = MsgBody
If ((MsgAttachment & "X" <> "X") _
And (fso.fileexists(MsgAttachment))) Then _
.Attachments.Add MsgAttachment
.Send
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If (Err.Number = 0) Then
Send_Mail = True
Else
Send_Mail = False
End If
Application.DisplayAlerts = True
End Function
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Tuesday, March 15, 2016 5:49 PM, Jorge Marques <[email protected]>
wrote:
Hi Paul,Yes I use Outlook, Having Outlook opened for it to work is not an
issue for me, it is always open from the moment computer is on until is shut
down. Providing the function would be more than helpful :). Thank you very much!
Best,Jorge
On 15 March 2016 at 19:15, Paul Schreiner <[email protected]> wrote:
First of all, Excel doesn't have email capability.What it DOES do, however, is
allow you to use your existing email software.
At least somewhat.
What email do you use?I use Outlook.With that, I can write a function that
takes the email address and filename as input and creates/sends the appropriate
email.
But you have to have Outlook open for it to work.
I can provide that function if it is helpful.
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Tuesday, March 15, 2016 11:28 AM, Jorge Marques <[email protected]>
wrote:
Hi all,I have been searching but haven´t found so far a macro similar to this
one, the situation point is:
Have one folder: C:\Users\username\Documents\Weekly Report\
With 4 different reports:
Report 1Report 2Report n...
for 4 different companies (Reports are the same but for different customers)
Then a Email recipient excel with the emails I should send these reports every
week
Is there a way for me to open the Email Recipient excel run a macro, and then
it sends 4 different emails with the respective reports attached to the
respective email recipient?
So Reports should be attached to an email separately from each and sent to
| [email protected]
Report 2 sent to
| [email protected] |
|
So 4 reports, 4 emails sent separately but same text (subject, description)
A 10000000 thanks in advance :).
--
Best Regards,Jorge Marques--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Best Regards,Jorge Marques--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
