Vba to attach a file using cell reference

[Revathi]

Hi all,

i need to attach a file in a outlook from my local path wherein the path keeps changing in a daily basis however it would be better for me if i do a changes in a cell reference instead of directly in the macro. below is the syntax which i directly keep changing the path in the macro which is not suitable for me.

2. and also i want to attach a file using the first 3 letter of the file name.

.Attachments.Add "Z:\COMMON\\2018\Mar\07\ATL.xlsx"

Anyone can help on this.

 

[bugmonsta]

If your value is in b1 (for example) then it should be Z:\common\2018\mar\07\atl.xslx - no quotes needed

change your attachments to and it should attach

 .attachments.Add Range("b1").Value

if it always on the Z drive then you can limit the amount you need to type by something like below, which means that b1 would only need 2018\mar\atl.xslx

.attachments.Add "Z:\common\" & Range("b1").Value

Hope this all makes sense, post back if note

 

[Revathi]

Thanks a lot..it works fine.. but is it possible that i can attach a file using the first 3 letter of the file name since i have dates in the file name wherein it keep changing daily. Hence it would be better for me if i refer the first 3 letter of the file name to attach.

 

[Perpa]

... it would be better for me if i refer the first 3 letter of the file name to attach.

Maybe this...

Put '2018\Mar\07\' without the quotes in cell B1
Put 'ATL' without the quotes in cell C1

Then replace this line...

.attachments.Add "Z:\common\" & Range("b1").Value

...with this line...

 .Attachments.Add "Z:\COMMON\" & cells(1,"B") & cells(1,"C") & ".xlsx"

 

[Revathi]

Thanks a lot perpa

 

[Perpa]

You are welcome.