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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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
 
Upvote 0
That's very close. Try:
Code:
.Attachments.Add Worksheets("parameters").Range("D3").Value
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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
Back
Top