Forum Discussion
brady
May 01, 2018Copper Contributor
VBA help for a Macro (inserting picture from file)
Hi, I am new to VBA and macros and I was hoping that someone could help me writing the VBA to insert a photo from your personal files. I created the button, but I just need to write the VBA to pu...
sumera1913
May 20, 2020Copper Contributor
After using the VBA how to break the link from images. As the file is shared through email and images will goes off
- Gstg72Nov 27, 2020Copper Contributor
I'm having the same issue as far as the link being inserted, but not the picture. So It can't be shared or sent via email to customers. Any thoughts on how to change this from a link insert to a picture insert?
Thanks so much for your time
- NikolinoDENov 28, 2020Gold Contributor
To be honest, I asked where it is best to include such a VBA code.
Most of them say with Outlook, not Excel.
So I searched the internet and found this information.
Folder selection when sending With this VBA code, any e-mail folder can be selected as storage when sending an e-mail. To use this example, please note the important information and the workshop Using VBA in Outlook.
Please copy the 1st part of the code into a new module (insert -> module in the VBA editor):
Option Explicit Public Function SentFolder (ByRef Item As Object) As Boolean '================================================== ===================== '' Displays the Outlook® selection folder when sending an email 'If required, e-mail to a folder other than "Sent Items" 'to discard. '(c) http://www.outlook-stuff.com '2008-11-19 version 1.0.1 '================================================= ===================== Dim obj Folder As Object '------------------------------------------------- -------------------- 'Although the SaveSentMessageFolder- 'property should also be possible for meeting requests 'there is a mistake with these. Hence, prior to using this 'Function asks whether the item is an email. '------------------------------------------------- -------------------- If Not Item.Class = olMail Then Exit Function '------------------------------------------------- -------------------- 'The loop is run through until a valid folder is selected 'or the selection is canceled. '------------------------------------------------- -------------------- do '------------------------------------------------- ---------------- 'Show folder selection '------------------------------------------------- ---------------- Set objFolder = Nothing Set objFolder = Outlook.Session.PickFolder '------------------------------------------------- ---------------- 'Has the selection been canceled? '------------------------------------------------- ---------------- If objFolder Is Nothing Then SentFolder = True Exit function End If '------------------------------------------------- ---------------- 'Wrong folder type selected? '------------------------------------------------- ---------------- If InStr (objFolder.DefaultMessageClass, "IPM.Note") = 0 Then Set objFolder = Nothing If MsgBox ("Please select a folder for emails." _ , vbCritical + vbOKCancel, "Select shelf") = vbCancel Then SentFolder = True Exit function End If End If '------------------------------------------------- ---------------- 'The inbox is not suitable as a shelf '------------------------------------------------- ---------------- If Not objFolder Is Nothing Then If objFolder = Outlook.Session.GetDefaultFolder (olFolderInbox) Then If MsgBox ("Are you sure you want to put the sent email in the inbox?" _ , vbExclamation + vbYesNo + vbDefaultButton2, "Select storage") = vbNo Then Set objFolder = Nothing End If End If End If Loop While objFolder Is Nothing '------------------------------------------------- -------------------- 'Define the storage location of the email '------------------------------------------------- -------------------- Set Item.SaveSentMessageFolder = objFolder '------------------------------------------------- -------------------- 'Delete reference to folder '------------------------------------------------- -------------------- Set objFolder = Nothing End function 'source: https://www.outlook-stuff.com/tipps-tricks/programmierung/287-ordnerauswahl-'beim-senden.html
The call is made from the Application_ItemSend event in the module ThisOutlookSession. Please copy the 2nd part of the code here:
Übersetzungstypen Textübersetzung Ausgangstext 908 / 5000 Übersetzungsergebnisse Private Sub Application_ItemSend (ByVal Item As Object, Cancel As Boolean) '================================================= ===================== 'This procedure is recorded immediately before an email is sent. ' call. If "Cancel" is true, the sending of the e-mail is canceled. '(c) http://www.outlook-stuff.com '2008-11-19 version 1.0.1 '================================================== ===================== '------------------------------------------------- -------------------- 'Show folder selection for storing the sent mail '------------------------------------------------- -------------------- Cancel = SentFolder (Item) '------------------------------------------------- -------------------- 'Delete reference to email '------------------------------------------------- -------------------- Set Item = Nothing End Sub
At the beginning I would not have thought that it could be so difficult.
In retrospect, however, I understand watum it's so hard.
It's not about Excel, it's about Outlook.
It doesn't really matter whether I send an Excel file or another file as an e-mail.
-------------------
Supplement:
(Step by step)
Outlook, start the VBA editor successfully.
I add Code 1 via "Insert / Module". In the drop-down menus in the pop-up window, (“Selection”), (“Declarations”) remains.
The left is now under Project1 (VbaProject.OTM)
- Microsoft Outlook objects
- ThisOutlookSession
- modules
- Module 1
If I now double-click “ThisOutlookSession”, a window opens again, the drop-down menu says “Application” on the left and “ItemSend” on the right.
I copy code 2 into the free field.
I save everything (without renaming anything), go back to Outlook, restart Outlook.
When I write an email now, I get various error messages:
First runtime error for "AddinTools Classic Menu for Outlook".
Then the VBA editor opens again, module 1 is marked on the left and the pop-up window reads:
"Error during compilation, invalid outside of a procedure, module 1 marked".
If I click away, I quit the debugger.
Don't forget to reactivate the macros.
----------------------------------------------
I hope that you will get a little further with it.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
- Gstg72Dec 02, 2020Copper Contributor
NikolinoDE Thank you for the very detailed response, but what I am looking for is much simpler than the posting.
I'm looking to simply run a macro that finds a picture's location in one cell and then insert the picture into another cell. But it has to be attached to a cell so it can be sorted AND be an actual picture vs a link.
I've tried 2 versions with each having it's own issues:
1 - ActiveSheet.Pictures.Insert(picname).Select ', LinkToFile:=msoFalse, SaveWithDocument:=msoTrue
** This one attaches to cells, and inserts the pictures, but ONLY as links, so the spreadsheet can't be sent to anyone.
2 - ActiveSheet.Shapes.AddPicture Filename:=picname, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=Cells(CurRow, PicLocCol).Left, Top:=Cells(CurRow, PicLocCol).Top, Height:=80, Width:=100
** This one attaches a picture (vs a link) and can be sent, but won't attach to cells, so it can't be sorted.