Hi Paul,
i have the Microsoft Outlook 15.0 Object Library.
The Macros runs fine if I remove the updates and display alerts, if not it
generates error.
'Application.ScreenUpdating = False
'Application.DisplayAlerts = False
This is great starting point, my challenge now besides the error is how do
I point 64 emails to 64 reports to 64 separate emails? I know there are not
miracles but can this be done automatically, and not running the macro
differently 64x? Or is that defined in the function below?
Thank you very much,
Jorge
On 16 March 2016 at 12:28, Paul Schreiner <schreiner_p...@att.net> wrote:
> 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 <leote.w...@gmail.com>
> 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 <schreiner_p...@att.net> 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 <leote.w...@gmail.com>
> 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 1
> Report 2
> Report 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
>
> u...@company1.com
>
> Report 2 sent to
>
> u...@company2.com
>
> 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 excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> 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 excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> 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 excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> 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 excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> 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 excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
