Excel Template sending e-mails

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Morning

I have been tasked with "fixing" and excel template created by someone else who no longer works for the company.

We fill in the template, press a "clicky" then it saves and send the completed template to the client.


The issue is that the company images that appeared in the E-mail are now replaced with "Tinypic" sad emoji advising that the image is not found, suggesting to me that the previous employee hosted said images off site (Not sure why), I would of thought it would of made sense to have Excel open a saved E-mail Template and use that.

So I need to solve that (I have what I understand to be the VBA password, though my VBA experience is limited)

Go forward I also need to, put in a method for a user (Manager) to change the address the e-mail gets sent to, should the need arise.

So, I am hoping that I can create a E-mail template for each of the Clients Excel templates and get the Excel template to use that instead of whatever it is currently using, and thus the managers can open and change that email template when the e-mail address need updating.

Thanks in advance.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

From your description of the problem you are facing ...

you only need to make sure your target image location is correct ...
 

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Okay, spent 30 mins trying to work out how to get up the VBA code, only to work out that the clicky had been deleted, thus there is no VBA code anymore.

So i am now here - https://www.makeuseof.com/tag/send-emails-excel-vba/

Reading on how to do it.

Problems
  1. the strFrom field - needs to be the person clicking the button e-mail, not a fixed e-mail
  2. strBody - "The text I place in between these quotations" is fine (I think, can I just hit enter for a new line for the text?) but I'm needing the file to attached itself to the e-mail<code></code>
  3. SMTP??
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
2019
Platform
Windows
If I were you I would rather to go to website showing how to do it with the following code:
Code:
Sub SubName()
Dim DistList As Variant
DistList = Array("name1","name2")
ActiveWorkbook.SendMail Recipients:=DistList, Subject:="subject"

End Sub
Not sure how it varies from the original code that I found, but so far it worked flawlessly.
 

Shockwave IIC

New Member
Joined
Jun 19, 2015
Messages
31
Okay, sorry more questions,

This is what I've got so far
Code:
Sub Template_to_Ecar()
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    TempFilePath = Environ$("X:\CONTROL\Folder name") & "\"
    [COLOR=#ffa500]TempFileName = Str(Sheet1.Cells(C7)) & "Text" & Str(Sheet1.Cells(C16))[/COLOR]
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
   On Error Resume Next
    With OutMail
        .To = "Removed for Data Protection"
        .CC = ""
        .BCC = ""
        .Subject = Str(Sheet1.Cells(C7)) & "Text" & Str(Sheet1.Cells(C16))
        .Body = ""
        Select Case Time
        Case Is < TimeValue("12:00")
            msg = "Good morning"
        Case Is < TimeValue("16:00")
            msg = "Good afternoon"
        Case Else
            msg = "Good evening"
    End Select
        .Body = "Please see attached completion details"
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
End Sub
Highlighted is the 1st (Of many no doubt) errors. I need the subject to be the same as file name, and I also think it will fail on the Body text as well.


EDIT to add
Will this just send a generica e-mail or will it send the users standard e-mail with signature?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,545
Messages
5,469,346
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top