How to disable "this documents contains links that may refer to other files...."

[jaxonvoice]

Hi All,

I am trying to automatically send an email via .oft, the code works except that when i disable any alerts the oft doesnt update.
is there a way to automatically update the links without the prompt " this document contains links that may refer to other files. do you want to update this doc..."

what im trying to do:
1. Run the code in excel
2. automatically Update the oft file that is link to excel and send

appreciate your help

Here's the code


Set OutlookApp = CreateObject("Outlook.Application")
myoft = ("C:\test.oft")

With OutlookApp.CreateItemFromTemplate(myoft)
'it prompts me to update the data, i want to get rid of this and update automatically

.to = "abc@xyz.com"
.Subject = "hello"
.display 'when i remove this the file does not update
.send

End With

 

[pbornemeier]

Welcome to the board.

Perhaps this will work.

Set Application.AskToUpdateLinks = False 'Turn off link question
Workbooks.Open Filename:= _
    "C:\Documents and Settings\john.smith\My Documents\Data.xls" 'Open the file referenced by text.oft
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources 'update links in that file
ActiveWorkbook.Save 'Save updated file
ActiveWindow.Close  'Close updated file
Set OutlookApp = CreateObject("Outlook.Application")
myoft = ("C:\test.oft")
With OutlookApp.CreateItemFromTemplate(myoft)
    .to = "abc@xyz.com"
    .Subject = "hello"
    .send
End With
Set Application.AskToUpdateLinks = True 'Reenable the link question

</PRE>

 

[jaxonvoice]

Hi Phil,

Thanks for your help, but still my oft file did not update...just to give you a little more overview, I used the "paste special>paste link> microsoft excel object" funtion in outlook, then i save this template...so whatever changes i made to my excel it will be captured on my oft....
it works fine when i click the update alert button manually, but i can't seem to get it automatically updated whenever i open it.

One question in the code?

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources 'is it updating the oft file or updating the links in the excel file from other sources?

thank you again for your inputs

 

[pbornemeier]

The code I posted will update the Excel file's links to other documents. I misread your question.

Although I could not determine how to update the attachment link, this code will remove the attachment from the instance of the message created by the template and reattach the current version of the file. Which seems to accomplish the same thing, albeit not as elegantly:

Sub test()
    Dim OutlookApp, myOft
    Dim myAttachments As Outlook.Attachments
    Set OutlookApp = CreateObject("Outlook.Application")
 
    myOft = ("C:\test.oft")
    With OutlookApp.CreateItemFromTemplate(myOft)
        Set myAttachments = .Attachments
        'Delete file from message just created and add latest version
        On Error Resume Next 'in case there is no attachment
        myAttachments.Remove 1 'Remove what is there
        On Error GoTo 0 'reset error handling
        myAttachments.Add "C:\File That Is Attachment In Test.oft"  'Attach file
        .To = "abc@xyz.com"
        .Subject = "Hello"
    End With
End Sub

http://www.rondebruin.nl/sendmail.htm contains lots of excellent information regarding using Outlook from Excel.

 

[jaxonvoice]

ADVERTISEMENT

Hi Phil,

thanks again for your reply and the link!...
What im trying to accomplish is similar to a code on this link
http://www.rondebruin.nl/mail/folder3/mail4.htm
it works fine, but i want to keep the cell format, size, width, font size exactly the same...however the code it converts excel to html and mail
the html file as a body... i assume that the convertion from excel to html
altered the cell size thats why it doesnt look exactly the same as what i have on my spreadsheet...or am i doing something wrong?

thanks!

 

[pbornemeier]

I'm sure the html conversion makes some modification to the exact formats in an Excel document.
Did the code provided in post #4 which detatched and reattached the attachment work? You will need to change this code :
"C:\File That Is Attachment In Test.oft"
to the path & name of the excel file that is in your .oft file.

 

[jaxonvoice]

Hi Phil,

Thanks, Yes it works...not as body of the email though, but as an attachment...but nevertheless this would do...

thank you so much for your help

Cheers!