Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
I use a code to embed image to excel (this part works fine), then how can I insert that image to an email body. Also I want to copy the text from column A of relative row (if I am in E3, then I want to copy A3 text) for the subject of email.
VBA Code:
Sub SelectOLE()
Dim objFileDialog As Office.FileDialog
Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)
objFileDialog.AllowMultiSelect = False
objFileDialog.ButtonName = "Select File"
objFileDialog.Title = "Select File"
objFileDialog.Show
If (objFileDialog.SelectedItems.Count > 0) Then
Set f = ActiveSheet.OLEObjects.Add _
(Filename:=objFileDialog.SelectedItems(1), _
Link:=False, _
DisplayAsIcon:=True, _
IconLabel:=objFileDialog.SelectedItems(1), _
Top:=ActiveCell.Top, _
Left:=ActiveCell.Left _
)
f.Select
With f
.ShapeRange.LockAspectRatio = msoFalse
.Width = ActiveCell.Width
.Height = ActiveCell.Height
End With
'f.Width = ActiveCell.Width
'f.Height = ActiveCell.Height
End If
EmailChase
End Sub
Sub EmailChase()
If ActiveWorkbook Is ThisWorkbook Then
Dim ws As Worksheet
Set ws = ActiveSheet
Dim emailApplication As Object
Dim emailItem As Object
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
Dim sp, ep As String
sp = "<p style='font-family:Tahoma;font-size:10pt;mso-margin-top-alt:0.0pt;margin-bottom:0.0pt'>"
ep = "</p><br>"
strbody = sp & "Dear Francis," & ep _
& sp & "User chases this case. Please review." _
& sp & "<br>" _
& ep
'Now insert the embedded picture here
On Error Resume Next
With emailItem
.Display
.To = "testingsample@gmail.com"
'.CC = sh.Range("P2").Value
'.BCC = sh.Range("Q2").Value
'.Subject = ws.Range("a2").Text
.Subject = ws.Range(Cells(Rows(), 1)).Text
.HTMLBody = strbody & .HTMLBody
End With
On Error GoTo 0
Set emailItem = Nothing
Set emailApplication = Nothing
End If
End Sub
