Excel Template sending e-mails

[Shockwave IIC]

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.

 

[James006]

Hi,

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

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

 

[Shockwave IIC]

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]

If I were you I would rather to go to website showing how to do it with the following 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]

Okay, sorry more questions,

This is what I've got so far

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?