open and send

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
Hello -

I know I'm asking a lot on this one, but as usual, I need some help.
I have a date in cell A1. B1 displays the day (ddd). What I need to do is based on the day e.g. tuesday, I need to go and email a file that was created for this date A1 -1. Is there a way to do this?

So if today is 1/27/05 and this date is in A1, I need the code that will open a file named blah1/26/05 and send it to a predefined email address.
I hope some of this makes sense.

thanks for your time,

Jim
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Something like this might do it for you. You'll have to put the file path in the code. You could put it in a Worksheet_Change() event as well:
Code:
Sub mailtesting()

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    If Range("B1").Value = "Tue" Then
        With OutMail
            .To = "somebody@domain.com"
            .Subject = "Here's your file"
            .Attachments.Add ("C:\blah" & Format(Date - 1, "mm-dd-yy") & ".txt")
            .Display   'or use .Send
        End With
    End If
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
Thanks Taz: Almost there.

I'm trying what you gave me with this:


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
'If Range("C2").Value = "Wed" Then
With OutMail
.To = "j#####.com"
.Subject = "testing"
.Attachments.Add ("Worksheets("parameters").Range("D3").Value")
'
.Display 'or use .Send
End With
'End If
Set OutMail = Nothing
Set OutApp = Nothing

If I can define the filename to send from a cell, I think I'm there.

Thanks for your time,

Jim
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
That's very close. Try:
Code:
.Attachments.Add Worksheets("parameters").Range("D3").Value
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top