Free Microsoft Excel 2013 Quick Reference

Email embedded file Results

Here is my scenario

I have a spreadsheet with a word and .xls file embedded in it.

I have a macro that creates an email and want to add each of these files.

Can't seem to get the attachment of the embedded images to work.

Any help welcome

Hi, I looked over this forum and I have tried using some of the help given to others to make macros to send and doc but I just can get it to work right, here is what I am trying to do.

------

title the email with name from A1 date from B2 and name from D2

I have 3 spots for emails D2, G2, D3 I want to be able to type emails into those cells and the macro send it to them via Outlook

Last is to insert the excel file into the body of the email not an attachment so when I open it I don't have to open a file to see my data.

---

This is my first time using macros, and I want to learn to how to get this working. because there is a lot I want to do.

Thank you for the help

I have a list of pdf/autocad drawings in excel. This list is hyperlinked to the actual pdf/autocad drawings on my network. I need to email this document to a user outside not on the network. I have to assume the person on the other end is computer illiterate. How can I embed the files into the excel document so the person on the other end would have access to the files? If I email it with hyperlinks to my network, they won't be able to gain access. I can hyperlink to the manufacturer's website but there are to many to go through. The only thing I can think of is a self extracting zip file, but that might not work either. Any ideas?

I'm using Excel 2003 on Windows XP. I created a table with some
outline groups (grouped up by row). I also embedded some Word and
email (msg) files into some of the cells using Insert->Object-
>CreateFromFile, unchecked "Link to file", and checked "Display as
icon". When I collapse a group, the icons for the embedded files
still linger, disembodied-like. Is there a way to make them go away
like the rest of the grouped rows when the group is collapsed?

Thx.

I'm asking about embedding a file specifically in a worksheet CELL, and not just dumped somewhere on the worksheet.

I'm looking to create a spreadsheet which contains the shipping details of our products. On of these columns needs to contain an embedded file representing the scanned signed shipping note (around 50k) - one embedded file for each row - there are only about 20-30 rows at a time.

The reason I want to embed the files is that I don't want 20-30 separate files to gather together and send in an email along with the spreadsheet (which would allow me to enter hyperlinks links instead). My problem is that I want to be able to sort and filter the rows, together with their relevant embedded files.

I think it would probably be easier to embed the files all on a different sheet and simply insert some sort of hyperlink in the relevant cell which linked to the embedded object (pdf file). I have no idea how to do this.

Also, can I get an adobe PDF icon to represent the embedded file without the object taking up any more space than the icon image (there seems to be a lot of blank space underneath the icon meaning that to be able to make out the icon the row height needs to be increased, making the worksheet look messy.

many thanks
Guy

Morning all.

hope all is well

I have come accross a little problem.

I been doing some digging around but i seem to be going in circle.

i have a spread sheet which is a job order form. you fill in the details and at the end of the excel sheet you press teh send button done via a macro and off it send.

here is the code i used

Sub eMailActiveWorksheet2()

Dim OL As Object
Dim EmailItem As Object
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name
For y = 1 To Len(FileName)
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "", "*", "?", """", "<", ">", "|"
Case Else
SaveName = SaveName & TempChar
End Select
Next y
ActiveSheet.Cells.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
ActiveWorkbook.SaveAs "C:" & SaveName
ActiveWorkbook.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = Sheets("sheet1").Range("m1")
.Body = "This is an Automated email, Can you please process this order for testing."
.To = "NowDeliveryTestCoord@cw.com"
.Importance = olImportanceHigh
.Attachments.Add "C:" & SaveName
.Send
End With
Kill "C:" & SaveName
ActiveWorkbook.Close False

Application.ScreenUpdating = True

Set OL = Nothing
Set EmailItem = Nothing

End Sub this is good untill some one embedded a file and then sent it on to me. as this only does a copy and paste and email the new spreadsheet it misses teh embedded files.

So i need to rework this code into a new one.

I need it so when you click send. it 1) Saves current Spreadsheet "c:Temp.xls". 2) attach Speadsheet to email and send. 3) Delete "c:temp.xls". 4) close and end on desktop.

i think i have lost the will to live as i have tried to edit the above code with no luck.

Any help greatly aprreciated

Thanks

Lee Keene

I've been left a workbook containing many sheets, most of which contain
several reference to particular cells in a previous month's copy of the
workbook.

So for instance if the current workbook is called say "Cash September
2005.xls", after this is 'rolled over' to the next month via a macro
which clears out several ranges, it saves as a new name "Cash October
2005.xls"

There are a hundreds of cells which are opening cash balances and which
are dependent on the previous month's workbook closing balance. So for
instance B10 on Sheet1 of the September Workbook contains a formula

='G:Cash[Cash August 2005.xls]Sheet1'!$B$11

When the September workbook is rolled over and becomes the October
Workbook, the formula in B10 still refers to August. I need to add some
code to the rollover macro so that the formula changes to:

='G:Cash[Cash September 2005.xls]Sheet1'!$B$11

i.e. I just need to change the reference to the month in the formula
since the layout of the workbook doesn't change. I've created variable
names which contain the text "Cash August 2005.xls" (lastfname), and
its equivalent "Cash September 2005.xls" (obalfname), and have tried to
use these in some Find and Replace code. However when the macro runs:
i.e.

Cells.Replace what:=Range("lastfname"), replacement:=Range("obalfname")

I get an Update pop up Window which is requiring me to select the
filename for each replace it finds. There are a hundred or so of these
balances to replace and I need the code to run untouched by human hands
as it were. (Incidentally does anyone know how to close these update
windows without having to select a file name? It was driving me mad
just now since the Cancel or X close didn't seem to have any effect,
and I had to step through all the Replaces on a sheet before the macro
would finish).

a) is this the best approach to adopt or can someone suggest a better
way? I'm thinking for instance that it might be better to move all the
closing balances into the opening balance cells as values, immediately
prior to the rollover so that I avoid links to other workbooks. In
which case I'd need to be able to identify all the opening and closing
balance cells, which might be more complicated than finding specific
text since it seems inelegant to have to create hundreds of individual
range names

b) if the Find/Replace is the best way to proceed, what code should I
use in order to replace these strings across all the relevant sheets,
so that the user doesn't have to continually click on file names. If it
makes any difference, not all the sheets have balances on them, but if
it's just as easy to loop through all the sheets I could live with
that.

If responding to the ng, please email to me as well.

Many thanks in advance.

Richard Buttrey

Hi guys!

Want to ask a question and this is a situation.

I have a form and want to send that form using e-mail (via VBA). Also have a .pdf document to embedded inside the file excel (that form). The problem is, after i already send the email attach with that form, the embedded file can't open by others user. I try embedded file .jpg. That file can open but pdf document can't. Can anybody help me with this situation?

Thanks!

st3vi3

Hi, i wish to email a spreadsheet to a client with a shockwave file. i am able to embed the shockwave file into the spreadsheet but it references the .swf from my desktop and if i send this workbook to my client, they would not be able to see it.

i would like to embed the file into a hidden sheet and reference this .swf from there. can somebody please tell me how to do this? i have no vba scripting experience.

outlook 2007 file attachment prompt

Greetings,
I had a vba function (macro) in outlook 2003 that i somehow lost. I am now running vba in Office 2007 and my function to attach a file to my email works, only not the way I want it to.  Here's the function:

Sub Meeting_Minutes() Dim sFileName As String Dim oApp As Outlook.Application Dim NewMail As Outlook.MailItem Dim colAttach As Outlook.Attachments Dim oAttach As Outlook.Attachment Dim oPA As Outlook.PropertyAccessor Const PR_ATTACH_MIME_TAG = "http://schemas.microsoft.com/mapi/proptag/0x370E001E" Const PR_ATTACH_CONTENT_ID = "urn:schemas:mailheader:content-id" Const PR_HIDE_ATTACH = "http://schemas.microsoft.com/mapi/id/{00062008-0000-0000-C000-000000000046}/8514000B" Set oApp = New Outlook.Application Set NewMail = oApp.CreateItem(olMailItem) sFileName = "Report_Minutes" & IIf(Month(Date) < 10, "0" & Month(Date), Month(Date)) & "-" & IIf(Day(Date) < 10, "0" & Day(Date), Day(Date)) & "-" & Right(Year(Date), 2) & ".pdf" With NewMail .Subject = "Minutes - " + FormatDateTime(Date) .To = "'EMAIL REMOVED" .CC = "EMAIL REMOVED" .BodyFormat = olFormatHTML .Attachments.Add "D:minutes" & sFileName .HTMLBody = BuildMinutesMessage() Set colAttach = .Attachments Set oAttach = colAttach.Add("C:Documents and SettingsmeApplication DataMicrosoftSignaturescorpimage001.jpg" .Save ' *** POSITION CRITICAL *** you must dereference the ' attachment objects before changing their properties Set colAttach = Nothing Set oAttach = Nothing ' set properties of the attached graphic that make ' it embedded and give it an ID for use in an tag Set colAttach = .Attachments Set oAttach = colAttach.Item(1) Set oPA = oAttach.PropertyAccessor oPA.SetProperty PR_ATTACH_MIME_TAG, "image/jpeg" On Error Resume Next oPA.SetProperty PR_ATTACH_CONTENT_ID, "myident" Set oPA = Nothing Set oPA = .PropertyAccessor oPA.SetProperty PR_HIDE_ATTACH, True .Display End With Set oAttach = Nothing Set colAttach = Nothing Set NewMail = Nothing Set oApp = NothingEnd SubLike I said, the function works. I just want it to present me with the filename of my attachment before it actually is attached. This was how I had the function originally before I lost my Hard Drive last week. (The reason for this is that the attachment is created using CUTEPDF and named by this routine using CutePDF. I used to copy and paste from my routine to name the file then click ok before the file was physically attached.)

Also, the pieces of the above code that try to attach my signature line fail to correctly attach the image from my signatures folder. I end up with an X like you see with a broken link to an image (but the image is there for the signature file (xml)).

Any enterprising VBA developers out there got any ideas here?have another Dr. Pepper

I've posted this on MrExcel.com here: http://www.mrexcel.com/forum/showthread.php?t=639331

I'm trying to embed an image file into the body of a Lotus Notes email. I have all of the code done except this piece. I've seen the previous forum threads regarding this, however, that approach doesn't seem to work with my code. Below is my code broken into the Notes session creation and the Email creation:

Notes Session Create:

	VB:
	
 
Declare Function ShowWindow& Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) 
 
Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ 
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
 
Private Function CreateNotesSession&() 
    Const notesclass$ = "Notes" 
     ' "Neues Memo - Lotus Notes"
    Const SW_SHOWMAXIMIZED = 3 
    Dim Lotus_Session As Object 
     
    Dim rc& 
    Dim lotusWindow& 
     
    Set Lotus_Session = CreateObject("Notes.NotesSession") 
     
    DoEvents 
    DoEvents 
    lotusWindow = FindWindow("Notes", vbNullString) 
    If lotusWindow  0 Then 
        rc = ShowWindow(lotusWindow, SW_SHOWMAXIMIZED) 
        rc = SetForegroundWindow(lotusWindow) 
        CreateNotesSession& = True 
    Else 
        CreateNotesSession& = False 
    End If 
End Function 
 
Sub CreateMailandAttachFileAdr(Optional IsSubject As String = "", Optional SendToAdr As Variant, _ 
    Optional CCToAdr As Variant, Optional BCCToAdr As String = "", Optional eAttach As Variant, _ 
    Optional BodyText As String) 
    Const EMBED_ATTACHMENT As Integer = 1454 
    Const EMBED_OBJECT As Integer = 1453 
    Const EMBED_OBJECTLINK As Integer = 1452 
     
    Dim s As Object ' use back end classes to obtain mail database name
    Dim db As Object '
    Dim doc As Object ' front end document
    Dim beDoc As Object ' back end document
    Dim workspace As Object ' use front end classes to display to user
    Dim bodypart As Object '
     
     
     ' checking if on citrix server or not
     ' if yes then asking the user to open lotus notes first
    On Error Goto err 
    Dim Lotus_Session As Object 
    Set Lotus_Session = CreateObject("Notes.NotesSession") 
    Goto start 
     
err: 
    Dim Path As String 
    Dim checkFile As String 
    Path = Environ("systemroot") & "system32srvmgr.exe" 
     'getting name of file
    checkFile = Dir(Path) 
    If Len(checkFile) > 0 Then 
        MsgBox "Please Open Lotus Notes in WTS Desktop" 
        Exit Sub 
    Else 
        Goto start 
    End If 
     
start: 
    Call CreateNotesSession& 
     
    Set s = CreateObject("Notes.NotesSession") 'create notes session
     
    Set db = s.GetDatabase("", "") 'set db to database not yet named
    Call db.OPENMAIL ' set database to default mail database
    Set beDoc = db.CreateDocument 
    Set bodypart = beDoc.CreateRichTextItem("Body") 
     
     ' Filling the fields
     '###################
    beDoc.Subject = IsSubject 
    beDoc.SendTo = SendToAdr 
    beDoc.copyTo = CCToAdr 
    beDoc.BlindCopyTo = BCCToAdr 
    beDoc.Signature = "" 
    beDoc.body = BodyText 
     '''''''''''''''''''''''''
     ''If you want to send a message to more than one person or copy or
     ''blind carbon copy the following may be of use to you.
     
     'beDoc.sendto = Recipient
     'beDoc.CopyTo = ccRecipient
     'beDoc.BlindCopyTo = bccRecipient
     
     ''Also for multiple email addresses you just set beDoc.sendto (or CopyTo or
     ''BlindCopyTo) to an array of variants each of which will receive the message. So
     
     'Dim recip(25) As Variant
     'recip(0) = "emailaddress1"
     'recip(1) = "emailaddress2"
     
     'beDoc.sendto = recip
     ''''''''''''''''''''''''
     
     ' beDoc.Body = "Hello Mary Lou, Goodbye heart"
     
    Set workspace = CreateObject("Notes.NotesUIWorkspace") 
     
     ' Positioning Cursor
     '###################
     
     
    Call workspace.EDITDOCUMENT(True, beDoc).GOTOFIELD("Body") 
     'Call workspace.EditDocument(True, beDoc).GotoField("Subject")
     
    Set s = Nothing 
     
End Sub 
[COLOR=#333333][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Email Creation:

	VB:
	
 LaunchMail() 
     
     'THIS LAUNCH THE EMAILING SYSTEM
    Dim emailTo(5) As Variant 
    Dim emailCC(5) As Variant 
    Dim emailAttach As Variant 
    Dim emailSubject As String 
    Dim emailBody As String 
    Dim Recipient As String 
    Dim i As Integer 
     '  Dim rs As ADODB.Recordset
     'SEQUENCE TO ISSUE TEAMREQUEST REPORT
     ''Gather mandatory created for the specific Sales Order
     '  Me.Requery
     '   i = 0
     '   Set rs = New ADODB.Recordset
     '   rs.Open "Select Email from teamone where SONumber='" & SONumber & "' and POLayer='" _
     '   & Forms!EditSalesOrder!PO & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
     '   Do While Not rs.EOF
     '     If IsNull(rs!Email) Then
     '       rs.MoveNext
     '      Else
     '         emailTo(i) = rs!Email
     '         i = i + 1
     '        rs.MoveNext
     '      End If
     '     Loop
     '     rs.Close
     
     'Generate bodies to fill lotus note fields
     
     'In this space I'll bring in my variables from Excel. The image file (.png) needs to be embedded in the body with other
text.
     
    img = "image here" 
     
    emailTo(1) = "me@me.com" 
     
    emailCC(1) = "" 
     
    emailSubject = "Test Message" 
     
    emailBody = "Hello World!" 
     
    Call CreateMailandAttachFileAdr(emailSubject, emailTo, emailCC, , , emailBody) 
     
End Sub 
[COLOR=#333333][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hope I made this clear enough. It's kind of time sensitive so any help is greatly appreciated.

Thanks all,
Austin

Howdy Group

I have a need to embed a wav file into a workbook. This is fairly simple to do through the insert>>object>>create from file, but I see no way to do this through VBA. I want the entire process to be simple so the user just has to click a button, pick their file from explorer, then play the sound file through the little speaker icon that is generally created.

In the end the one sheet (filename.xls) is the only thing sent through email and it must contain the wav embedded inside without using the insert>>object>>create from file route.

Im stuck

I am trying to send en email via Lotus Notes using VBA (in Access) but I am unable to get the email to send from a different mail database other than my personal one.

I have tried to explicitly enter the ServerName and DatabaseName in the following section


	VB:
	
 Maildb = session.GETDATABASE("ServerName", MailDbName) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The below excerpt displays the entire code which still sends via my personal Mail Inbox:


	VB:
	
 _ 
    String, bodytext As String, SaveIt As Boolean) As Boolean 
    Dim Maildb        As Object 
    Dim MailDbName    As String 
    Dim MailDoc       As Object 
    Dim AttachME      As Object 
    Dim session       As Object 
    Dim EmbedObj      As Object 
     
    On Error Goto err_SendNotesMail 
     
     ' Start a session to notes
    Set session = CreateObject("Notes.NotesSession") 
     
     ' Get the sessions username and then calculate the mail file name
     ' You may or may not need this as for MailDBname with some systems you
     ' can pass an empty string
    MailDbName = "MyDatabaseName.nsf" 
     
     ' Open the mail database in notes
    Set Maildb = session.GETDATABASE("ServerName", MailDbName) 
    If Maildb.IsOpen = True Then 
         'Already open for mail
    Else 
        Maildb.OpenMail 
    End If 
     
     ' Set up the new mail document
    Set MailDoc = Maildb.CreateDocument 
    MailDoc.Form = "Memo" 
    MailDoc.sendTo = recipient 
    MailDoc.Subject = Subject 
    MailDoc.Body = bodytext 
    MailDoc.SaveMessageOnSend = SaveIt 
     
     ' Set up the embedded object and attachment and attach it
    If attachment  "" And Dir(attachment)  "" Then 
        Set AttachME = MailDoc.CreateRichTextItem("Attachment") 
        Set EmbedObj = AttachME.EmbedObject(1454, "", attachment, "Attachment") 
         'MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If 
     
     ' Send the document
    MailDoc.Send 0, recipient 
    Maildb.Close 
     ' Clean Up
    Set Maildb = Nothing 
    Set MailDoc = Nothing 
    Set AttachME = Nothing 
    Set session = Nothing 
    Set EmbedObj = Nothing 
    SendMail = True 
     
end_SendNotesMail: 
    Exit Function 
     
err_SendNotesMail: 
    Select Case Err.Number 
Case 429: 
        MsgBox "Error: " & vbCrLf & Err.Description & vbCrLf & "Possible " & _ 
        "cause: Lotus Notes not installed", vbCritical, "Error while initializing LotusNotes" 
Case Else: 
        MsgBox Err.Description & Err.Number, vbCritical, "Error Lotus Notes" 
    End Select 
    Resume end_SendNotesMail 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,
I 've tried posting before but got no reply...

I have been sent an excel file containing a lot of macros. In the file there are a number of clickable cells, and when you click on one of the cells it displays two images (jpg files) in another worksheet. You then click on another button to take you back to the original worksheet.

The problem I have is that I'd like to change some of the pictures so that when I click on a cell in the first worksheet, it opens up my own images rather than the one's that have been pre-programmed.

I presumed that I would be able to go into the scripts and change the location of the images, but I can't find where the images are in the first place! The excel file is over 1mb in size, so the images must be embedded somewhere within the spreadsheet.

In one of the scripts it does refer to the images - i.e. "image009.jpg" as per code below:

Code:
 
    
    
     
    Bitmap 
   
nb. there's loads more code than this - there are 5 scripts in total - the above is the only place the images (i.e. "image009.jpg") are referenced.

When I try and search for "image009.jpg" on my computer it is nowhere to be found. However, when I go into the script editor, all the images appear in the "Documents & Settingsmy userAppication DataMicrosoftMse1033" folder!!! - but I cannot change them there because it says that the file is already in use...and as soon as I close down the script editor the files & directory dissapear!

Does anyone know where within the excel file these .jpg files have been installed? Is there any way that I can change them at all?

Hope someone can help.
Thanks.

ps. I can send the original file to someone over email if it will help...

Hi,

I used INSERT / OBJECT to insert embedded pdf objects into excel
spreadsheet. I even changed the icon name of pdf file image in this
spreadsheet. I noticed that I can drag and drop this embedded object
on to my desktop. However, when I do so, it changes the file
name...so what used to be named George (2001) changes now to Scrap
(1).shs. I can still open the file and it is a pdf format but it
hasn't retained the name and the icon that I had clicked on does not
look like a pdf icon, it looks like a "scrap" of paper.

I emailed this Scrap(1).shs in outlook and get the following error:

"This item contains attachments that are potentially unsafe.
Recepients using Microsoft Outlook may not be able to open these
attachments. Do you want to send anyway?"

How can I retain the icon name (George (2001)) and retain the pdf file
extension when I drag and drop on the desktop?

Any help here would be great. It would be great to be able to drag
and drop on to desktop and would like to make this possible.

S

I have a user that went to file, Send To, Email recipient in Office XP. She
put the names of the people to send it to and then sent the document. She
then closed out of the document. She later went back into the excel file and
the Email toolbar was still in the excel file (to:, cc:, subject). Is there
an option to turn this off?? Thanks.

I also opened the file and I see the same thing, although I am in 2003.

Shane

Is there anyway way to package an Excel 2003 workbook & and a Word 2003
document so that they can be emailled without all the links between the files
being broken?

I have tried embedding one file in the other (both ways - Word doc in
excell, which loses headers & formatting, & Excel in Word, which breaks all
the links when the file is closed) but this is not satisfactory.

I'm using a worksheet with a series of dropdown boxes to generate a quote &
output the quote as a text string to a single cell. The cell is then linked
to the word document which is our quotation form.

Hi,

I'm trying to set up an excel document so the user can drag and drop and
email from outlook into the spreadsheet as an embedded object. When I try to
drag an email now, it just copies the Subject, From, and To sections.

Thanks

Dan

Hi! I have several CSV files that I am exporting from a server, and I bundle
them with a pre-formatted XLS that has an Auto-Open macro that brings them
all into the spreadsheets in the appropriate location. The whole package is
zipped up and emailed to my users in a batch process. I'd like them to unzip
these files to their location of choice and just click on the XLS, but I
don't know the name of the path that they're going to use when they unzip
them, I just assume they will all go into the same folder or desktop
location, the File.XLS, File1.CSV, File2.CSV, etc., all together.

My problem is that when the macro tries to execute the background query
refreshes, it uses an embedded hardcode to the path that I last used when I
saved the file. I have tried stripping out the specifics, e.g., to leave
only the FILE1.CSV information without the path, but it doesn't work. Is
there a way that I can retrieve the File/Properties/Location information for
the XLS and use that path information, minus the file name itself, and stuff
that into my macro, appending each of the CSV file names? Thanks for your
help!

Hi,

I have an html file which has an swf flash movie embedded in it. When I open
it in excel, it works fine and the movie is viewable. However, when I want to
send it out as an email, everything but the embedded swf is viewable, i.e.
where the swf should be instead there is a blank white space!
And yet when viewing the html file through a browser (IE6) it is perfectly
fine.
Here's the affected code - as rewritten by excel...

I've tried playing around with the filepaths and attaching the .emz file
(which i believe to be a zip of the movie file) excel created and refers to
in the above code - but to no avail?!?!

Please can someone help.

thanks in advance.


No luck finding an answer? You could always try Google.