Keep getting a Compile Error Sub or Function Not Defined

ffbaker632

New Member
Joined
Jan 23, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to run this email code and I'm getting a Compile Error Sub or Function not defined, what am I doing wrong?


Code:
Sub Mail_small_Text_And_JPG_Range_Outlook()
    'This macro use the function named : CopyRangeToJPG
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim MakeJPG As String

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

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = ""
    
    MakeJPG = Battalion_1_Daily_Staffing("Sheet1")("A1:I27")

    If MakeJPG = "" Then
        MsgBox "Something go wrong, we can't create the mail"
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        Exit Sub
    End If

    On Error Resume Next
    With OutMail
        .To = .Union("abc@domain.com")("xyz@gmail.com")
        .CC = ""
        .BCC = ""
        .Subject = "Battalion 1 Daily Staffing"
        .Attachments.Add MakeJPG, 1, 0
        'Note: Change the width and height as needed
        .HTMLBody = "<html><p>" & strbody & "</p><img src=""cid:NamePicture.jpg"" width=750 height=700></html>"
        .Display 'or use .Send
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub



Function CopyRangeToJPG(NameWorksheet As String, RangeAddress As String) As String
    Dim PictureRange As Range

    With ActiveWorkbook
        On Error Resume Next
        .Worksheets("Sheet1").Activate
        Set PictureRange = .Worksheets(Sheet1).Range("A1:I27")
       
        If PictureRange Is Nothing Then
            MsgBox "Sorry this is not a correct range"
            On Error GoTo 0
            Exit Function
        End If
       
        PictureRange.CopyPicture
        With .Worksheets("Sheet1").ChartObjects.Add(PictureRange.Left, PictureRange.Top, PictureRange.Width, PictureRange.Height)
            .Activate
            .Chart.Paste
            .Chart.Export Environ$("temp") & Application.PathSeparator & "NamePicture.jpg", "JPG"
        End With
        .Worksheets("Sheet1").ChartObjects(.Worksheets("Sheet1").ChartObjects.Count).Delete
    End With
   
    CopyRangeToJPG = Environ$("temp") & Application.PathSeparator & "NamePicture.jpg"
    Set PictureRange = Nothing
End Function
 

Attachments

  • 2021-01-23.png
    2021-01-23.png
    152.2 KB · Views: 3
Last edited by a moderator:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ffbaker632

New Member
Joined
Jan 23, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
And what do I need to do to attach this Macro to a button?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Looks like you have used Ron de Bruin's code from here but have made some changes to both his text and his syntax

Ron's code line
Rich (BB code):
MakeJPG = CopyRangeToJPG("Sheet1", "A1:H50")


Your code line
Rich (BB code):
MakeJPG = Battalion_1_Daily_Staffing("Sheet1")("A1:I27")
 

ffbaker632

New Member
Joined
Jan 23, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I did I need the code to pull that specific range, should I leave the code exactly as is other than changing the range?

Welcome to the MrExcel board!

Looks like you have used Ron de Bruin's code from here but have made some changes to both his text and his syntax

Ron's code line
Rich (BB code):
MakeJPG = CopyRangeToJPG("Sheet1", "A1:H50")


Your code line
Rich (BB code):
MakeJPG = Battalion_1_Daily_Staffing("Sheet1")("A1:I27")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I did I need the code to pull that specific range, should I leave the code exactly as is other than changing the range?
If you look through Ron's code you will see a functioned called 'CopyRangeToJPG'. You have changed that to' Battalion_1_Daily_Staffing' and since the code does not have a function with that name you would get the error message you quoted. You would need to leave the function name as it was.

Ron's function has two string arguments being the sheet name and the range with those two string separated by a comma. You have changed that comma to closing and opening parentheses.

If correcting those two things doesn't solve your problem then I would suggest that you make contact with Ron since it is his code.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,154
Members
416,368
Latest member
PaoloC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top