Solved: word doc as attachement

[gibbo1715]

Hi all

I have a button on an excel form to create an email and attach a word document that i am using as a template, this I am ok with and have working fine

I also have a value in cell A1 say "12345" as an example

My question is, is it possible to attach my template word document but for it to be named as per the value in cell A1?

See the code below for how im getting things working so far

Dim AppOutlook As Object
Dim StrAttach As String

StrAttach = App.Path & "/Renewal.doc"
Set AppOutlook = CreateObject("Outlook.application")

With AppOutlook.CreateItem(olMailItem)
'Send Message To
.To = ""
'Subject
.Subject = ""
'Body Text
.Body = "Due for something or overdue etc......"
'Display the message
.Display
.Attachments.Add StrAttach
End With
Set AppOutlook = Nothing

Cheers

Gibbo

[mvidas]

Do you mean you want to email 12345.doc instead of Renewal.doc? Dim AppOutlook As Object
Dim StrAttach As String

StrAttach = App.Path & Range("A1").Text & ".doc"
Set AppOutlook = CreateObject("Outlook.application")

With AppOutlook.CreateItem(olMailItem)
'Send Message To
.To = ""
'Subject
.Subject = ""
'Body Text
.Body = "Due for something or overdue etc......"
'Display the message
.Attachments.Add StrAttach
.Display
End With
AppOutlook.Quit
Set AppOutlook = NothingThat should work fine then
Matt

[gibbo1715]

thanks matt,

My renewal.doc is a template form and i will need to email it over and over, but i need if possible to rename the renewal.doc attachment to 12345.doc before i send the email

cheers

gibbo

[Norie]

What about something like this?


Name App.Path & "/Renewal.doc" As App.Path & "/" & Range("A1").Text & ".doc"

StrAttach = App.Path & "/" & Range("A1").Text & ".doc"


By the way I'm not 100% sure what you want here as you mention a template(.dot), but you are attaching a document (.doc).

[gibbo1715]

Thanks Norie but I need to keep my original document called Renewal.

I have a document called Renewal.doc. It is a template document but because im emailing it to folk i ve left it as a .doc instead of a .dot so I can rename it and send it as an attachement from my code

So what I need is for the attachement to be renamed but the renewal.doc to remain as being called renewal.doc

Using your method i can always rename it back after i ve sent it so it does solve my problem but was wondering what other methods are available to me.

I know i can also copy the doc using and then kill my copy when sent

FileCopy App.Path & "\Renewal.doc", App.Path & "12345" & ".doc"

thanks for your patience

gibbo

[mvidas]

Hi Gibbo,

Sorry about the delay, but it looks like you already got the answer I would have given (first FileCopy, then Kill).

Matt

[gibbo1715]

Thanks Matt

I ll mark this as solved then

Gibbo

[Norie]

Gibbo

There is a property of an Attachment called DisplayName, that allows you to set what is displayed below the attachment.

I don't know if that will do what you want.

[gibbo1715]

Norie

How do I use this property?

tried as per below but states it doesnt support this object?

.Attachments.Add TestDoc
.Attachments.DisplayName "Attach Name"

[mvidas]

Gibbo,
I haven't tried this, as I've never used the DisplayName property, but it should work: With .Attachments.Add(TestDoc)
.DisplayName "Attach Name"
End WithMatt

[Norie]

Gibbo

It's a property of an Attachment, not the Attachments collection.

Try the code Matt posted, but I think you might need an = in there.


With .Attachments.Add(TestDoc)
.DisplayName="Attach Name"
End With

[mvidas]

Wow, forgot an equals sign :) Sorry about that, hadn't had much coffee yet at that point :P